Creating procedure inside IF section

问题:

I need some help with simple SQL code:

DECLARE @procExists int
SET @procExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'Table_Exists' AND ROUTINE_TYPE = 'PROCEDURE')
IF NOT @procExists > 0 
BEGIN
    -- test query
    -- SELECT 'Something' = @procExists;

    -- error throwing code
    -- CREATE PROCEDURE Table_Exists
    --     @schemaName varchar(50),
    --     @tableName varchar(50)
    -- AS
    --     RETURN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName)
END

The simple code above:
- declares an int variable
- checks if procedure dbo.Table_Exists exists
- IF NOT exists it creates it

My problem is this error information:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@schemaName".

I don't know why, but..
- when i execute 'CREATE PROCEDURE' body alone it works
- when i execute whole IF section excluding 'CREATE PROCEDURE' body, simple query works
- when i execute whole IF section including 'CREATE PROCEDURE' body, error is thrown

What am i missing?

回答1:

CREATE PROCEDURE has to be in it's own batch

So, dynamic SQL is one way:

IF OBJECT_ID('Table_Exists') IS NULL
BEGIN
    EXEC ('CREATE PROCEDURE Table_Exists
         @schemaName varchar(50),
         @tableName varchar(50)
     AS
         RETURN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName)
')
END

or DROP first

IF OBJECT_ID('Table_Exists') IS NOT NULL
  DROP PROC Table_Exists
GO
CREATE PROCEDURE Table_Exists
         @schemaName varchar(50),
         @tableName varchar(50)
     AS
         RETURN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName)
GO

Note the use of OBJECT_ID to see if the proc exists.

回答2:

You can do this using SET NOEXEC ON. This instructs SQL Server to ignore all SQL code until SET NOEXEC OFF is reached.

IF EXISTS (SELECT *
          FROM INFORMATION_SCHEMA.ROUTINES
          WHERE ROUTINE_TYPE = 'PROCEDURE'
          AND ROUTINE_SCHEMA = 'dbo'
          AND ROUTINE_NAME = 'HelloWorld')
BEGIN
    SET NOEXEC ON
END
GO

CREATE PROCEDURE dbo.HelloWorld
AS
    PRINT 'Hello world'
GO

SET NOEXEC OFF
GO

回答3:

From MSDN:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

Therefore, what you are trying to do is not possible, unless you are fine with implementing it via a dynamic query.

回答4:

if OBJECT_ID('PROC1') IS NULL  
   EXEC('CREATE PROCEDURE DBO.PROC1 AS SELECT 1')

GO

ALTER PROCEDURE DBO.PROC1(@PARAM1 INT, @PARAM2 INT)

AS

.................