SQL: Can't Create SP Inside If Block

Question

I am attempting to run some SQL queries if a table doesn't already exist, specifically:

1. Create the table.
2. Add some data.
3. Create a stored procedure to access the data.

Step 1 and 2 work fine, but when I try to do the third part, I get an error. Here is my code (don't need to read thoroughly - here for reference, skip to problem area below):

USE [MyDB]
GO


IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='fooTable' and xtype='U')
    BEGIN
        CREATE TABLE fooTable
        (
            ID INT NOT NULL IDENTITY(1,1),
            SomeNumber INT NOT NULL, 
            SomeOtherTable_ID INT NOT NULL, 
            PRIMARY KEY (ID),
            FOREIGN KEY (SomeOtherTable_ID) REFERENCES SomeOtherTable(SomeOtherTable_ID)
        );

        INSERT INTO fooTable (SomeNumber, SomeOtherTable_ID)
        VALUES (5, 1), (10, 1), (25, 1), (50, 1), (100, 1), (500, 1)

        CREATE PROCEDURE myProcedureName
        AS
        SELECT SomeNumber from [fooTable]
    END 

The relevant trouble line is the third one here, it works fine OUTSIDE of the if statement:

CREATE PROCEDURE myProcedureName
AS
SELECT SomeNumber from [fooTable]

but if I try to run it in the if block, SELECT gets underlined in red with the following:

Incorrect syntax near SELECT. Expecting EXTERNAL.

How do I run the stored procedure inside the if block? If it's relevant, this is in SQL Server using T-SQL.

Answer 1

Stored procedures, triggers, functions, and several other object types need to be in their own batch. This can be achieved within an IF block by wrapping the CREATE statement in an EXEC(N'....'):

IF (OBJECT_ID(N'dbo.footTable') IS NULL)
BEGIN
   CREATE TABLE dbo.[fooTable]
   (
      ID INT NOT NULL IDENTITY(1, 1)
           CONSTRAINT [PK_fooTable] PRIMARY KEY,
      SomeNumber INT NOT NULL, 
      SomeOtherTable_ID INT NOT NULL
           CONSTRAINT [FK_fooTable_SomeOtherTable] FOREIGN KEY
           REFERENCES dbo.SomeOtherTable(SomeOtherTable_ID)
   );

   INSERT INTO dbo.fooTable (SomeNumber, SomeOtherTable_ID)
   VALUES (5, 1), (10, 1), (25, 1), (50, 1), (100, 1), (500, 1);

   EXEC(N'
        CREATE PROCEDURE dbo.myProcedureName
        AS
        SELECT SomeNumber FROM [fooTable];
   ');
END;

Notes:

• Be sure to include the capital-N prefix on string literals containing dynamic SQL (as a best practice -- yes, not having it often works, but having it always works).

• Don't use deprecated compatibility views such as sysobjects. Starting with the release of SQL Server 2005, the proper system catalog views are in the sys schema. In this case, it would be sys.objects.

• You should schema-qualify the objects: dbo.fooTable instead of just fooTable

• You should name your constraints: PK, FKs, etc.

Answer 2

CREATE PROCEDURE needs to be in its own block. Move it out and do something like:

IF EXISTS ( SELECT * FROM sys.procedures  WHERE NAME = 'myProcedureName' AND type = 'P')
     DROP PROCEDURE myProcedureName
GO
CREATE PROCEDURE myProcedureName
AS
SELECT SomeNumber from [fooTable]

Answer 3

I like the following idiom:

if object_id('dbo.yourProc') is not null --object exists
   set noexec on;
go
create procedure dbo.yourProc as
begin
   select 'not implemented' as [message]
end
go
set noexec off;
go
alter procedure dbo.yourProc as
begin
   «actual definition here»
end
go

What I like about this is that preserves any permissions that already exist on the procedure and is idempotent.