VSO VSO - 1 month ago 8
SQL Question

SQL: Can't Create SP Inside If Block

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

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 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)

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

Notes:

  • You should schema-qualify the objects: dbo.fooTable instead of just fooTable
  • You should name your constraints: PK, FKs, etc.