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 = 'db开发者_Python百科o' 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?

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.

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

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.

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

GO

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

AS

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