I am working on a database generation script. In order to protect certain
tables from regeneration I am checking to see if they already exist. But I
am getting an error from my proc. I am sure its something silly..but my SQL
as far as table creations and such isnt' that strong...can I get some
alternate eyes? Thanks --Eric

/****** Object:  StoredProcedure [dnd_UserDelete]    Script Date: 9/5/2006
2:47:03 PM ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dnd_UserDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    CREATE PROCEDURE [dnd_UserDelete]
(
@ID bigint
)
AS
BEGIN
SET NOCOUNT OFF
DECLARE @Err int

--ENTER CUSTOM DELETE QUERY HERE

SET @Err = @@Error

RETURN @Err
END
GO

-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: dnd_UserDelete Succeeded'
ELSE PRINT 'Procedure Creation: dnd_UserDelete Error on Creation'
GO

First, you mentioned TABLE creation, but your example is about a PROCEDURE creation.

In either case, the Existence check 'could' be a little easier to read and understand...
(Use INFORMATION_SCHEMA.TABLES to check for a Table's existence.)

Here is a simplistic idea...

For a stored Procedure...

IF EXISTS
   (  SELECT ROUTINE_NAME
      FROM   INFORMATION_SCHEMA.ROUTINES
      WHERE  ROUTINE_NAME = 'dnd_UserDelete'
   )
   DROP PROCEDURE dbo.dnd_UserDelete
GO

CREATE PROCEDURE dbo.dnd_UserDelete
   ( @ID   bigint )
AS
   BEGIN
      SET NOCOUNT OFF
      DECLARE @Err int

      --ENTER CUSTOM DELETE QUERY HERE

      SET @Err = @@Error

      RETURN @Err
   END
GO

IF @@Error = 0
   PRINT 'Procedure Creation: dbo.dnd_UserDelete Succeeded'
ELSE
   PRINT 'Procedure Creation: dbo.dnd_UserDelete Error on Creation'



GRANT EXECUTE ON dbo.dnd_UserDelete TO {UserRole}
GO

For a table...

IF NOT EXISTS
   (  SELECT TABLE_NAME
      FROM   INFORMATION_SCHEMA.TABLES
      WHERE  TABLE_NAME = 'MyTable'
   )
   CREATE TABLE dbo.MyTable
      (   Table_ID   bigint   
        , Column2    varchar(20)
        , etc
      )
GO

IF @@Error = 0
   PRINT 'Table Creation: dbo.MyTable Succeeded (or skipped)'
ELSE
   PRINT 'Table Creation: dbo.MyTable Error on Creation'


Show quoteHide quote

Thanks Arnie...and sorry for my mistyping...you are correct it was for a StoredProcedure not a table.

  First, you mentioned TABLE creation, but your example is about a PROCEDURE creation.

  In either case, the Existence check 'could' be a little easier to read and understand...
  (Use INFORMATION_SCHEMA.TABLES to check for a Table's existence.)

  Here is a simplistic idea...

  For a stored Procedure...

  IF EXISTS
     (  SELECT ROUTINE_NAME
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_NAME = 'dnd_UserDelete'
     )
     DROP PROCEDURE dbo.dnd_UserDelete
  GO

  CREATE PROCEDURE dbo.dnd_UserDelete
     ( @ID   bigint )
  AS
     BEGIN
        SET NOCOUNT OFF
        DECLARE @Err int

        --ENTER CUSTOM DELETE QUERY HERE

        SET @Err = @@Error

        RETURN @Err
     END
  GO

  IF @@Error = 0
     PRINT 'Procedure Creation: dbo.dnd_UserDelete Succeeded'
  ELSE
     PRINT 'Procedure Creation: dbo.dnd_UserDelete Error on Creation'



  GRANT EXECUTE ON dbo.dnd_UserDelete TO {UserRole}
  GO

  For a table...

  IF NOT EXISTS
     (  SELECT TABLE_NAME
        FROM   INFORMATION_SCHEMA.TABLES
        WHERE  TABLE_NAME = 'MyTable'
     )
     CREATE TABLE dbo.MyTable
        (   Table_ID   bigint   
          , Column2    varchar(20)
          , etc
        )
  GO

  IF @@Error = 0
     PRINT 'Table Creation: dbo.MyTable Succeeded (or skipped)'
  ELSE
     PRINT 'Table Creation: dbo.MyTable Error on Creation'


  --
  Arnie Rowland, Ph.D.
  Westwood Consulting, Inc

  Most good judgment comes from experience.
  Most experience comes from bad judgment.
  - Anonymous


Show quoteHide quote

Hi,

I tried running scripts along this line ... a problem I hit was that a
CREATE PROCEDURE etc. statement has to be the first in a batch.

So the following would work ...

IF EXISTS ...

DROP ...

but the following would not ...

IF NOT EXISTS ...

CREATE ...

The following semantics might work better ...

IF NOT EXISTS ....
.... sp_executesql 'CREATE PROCEDURE ... AS RETURN'
GO
ALTER PROCEDURE ....


..... In theory, this would ensure existing procedures are never dropped, and
only altered if the ALTER statement is valid (so something that works is
never replaced with something that might not)

Any comments on the above?

A CREATE TABLE statements should always fail if the table exists already
(but never say never I guess) ... so testing for existence should be
superfluous. Its only dangerous when including a drop table prior to a table
creation statement, but is this ever necessary?


Show quoteHide quote

Dear Eric,
For this limitation, I'm consulting SQL Team now and I'll let you know the
reason as soon as possible.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

I got it. The CREATE PROCEDURE statement cannot be combined with other
Transact-SQL statements in a single batch. You can find this statement in
SQL Server Books Online "CREATE PROCEDURE".

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Sincerely,
Charles Wang
Microsoft Online Community Support

The issue here that CREATE PROCEDURE must be the first statement in a batch.
Also, you can use dynamic SQL:
IF NOT EXISTS (
    SELECT  routine_name
    FROM    information_schema.routines
    WHERE   routine_name = N'spMTUpdateDomain'
    AND     routine_schema = N'dbo'
    AND     routine_type = N'PROCEDURE')
        EXEC (N'CREATE PROCEDURE dbo.spMTUpdateDomain AS SELECT
GETUTCDATE()')
GO

ALTER PROCEDURE dbo.spMTUpdateDomain
...

Sincerely,
Charles Wang
Microsoft Online Community Support

Thanks thats just what I needed.

Eric
Show quoteHide quote

Identity column as a foreign key - help needed in logic
Primary Key
Postcode problem
Query Problem
DATEPART Gives different answers on different machines?!