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?
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.
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 you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With