Along with freelancing, consulting, I also spend time in replying to my blog comments to learn something new. Few such conversations end up in business deals, few in emails and few in the blog. This blog originates from one of such conversation regarding incorrect syntax.
Hi Pinal,
I am developing with ABCD, Inc. We are responsible to maintain software and released patches. The software uses SQL Server as back-end. I have created a script to update my application database for a new functionality. In this update i need to alter a stored procedure. I have an ALTER PROCEDURE script that works fine, but I need to check if the procedure exists and do some more stuff. Here are the errors.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘PROCEDURE’.
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable “@input”.
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword ‘PROCEDURE’.
Msg 134, Level 15, State 1, Line 24
The variable name ‘@output’ has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable “@input”.
What am I doing wrong here? The script is as follows (over simplified):
IF NOT EXISTS
(
SELECT name FROM sys.procedures WHERE Name = 'MyProc'
)
BEGIN
CREATE PROCEDURE MyProc
@input int = 120
AS
BEGIN
DECLARE @output DATETIME
SELECT @output = DATEADD(MINUTE, -@input, GETDATE())
END
END
ELSE
BEGIN
ALTER PROCEDURE MyProc
@input int = 120
AS
BEGIN
DECLARE @output DATETIME
SELECT @output = DATEADD(MINUTE, -@input, GETDATE())
END
END
I replied with the solution.
SOLUTION/WORKAROUND
I think you can’t alter the procedure inside the IF clause Try using it as a dynamic SQL. Here is the modified version. I have enclosed CREATE and ALTER under EXEC.
IF NOT EXISTS
(
SELECT name FROM sys.procedures WHERE Name = 'MyProc'
)
BEGIN
EXEC('CREATE PROCEDURE MyProc
@input int = 120
AS
BEGIN
DECLARE @timestamp DATETIME
SELECT @timestamp = DATEADD(MINUTE, -@input, GETDATE())
END')
END
ELSE
BEGIN
EXEC('ALTER PROCEDURE MyProc
@input int = 120
AS
BEGIN
DECLARE @timestamp DATETIME
SELECT @timestamp = DATEADD(MINUTE, -@input, GETDATE())
END')
END
I hope this would help someone to save time and energy.
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
No. I don’t think so.
Even this code does not work.
/**********************************************************/
IF NOT EXISTS
(
SELECT name FROM sys.procedures WHERE Name = ‘MyProc’
)
BEGIN
CREATE PROCEDURE MyProc
@input int = 120
AS
BEGIN
DECLARE @output DATETIME
SELECT @output = DATEADD(MINUTE, -@input, GETDATE())
/**********************************************************/
It’s working fine in Sql Server 2008
I prefer the old fashioned way, where we always modify the sproc. But that’s just me.
IF OBJECT_ID(‘dbo.MyProc’ , ‘P’) IS NULL BEGIN
EXEC(‘CREATE PROCEDURE MyProc AS
BEGIN
SELECT 1 AS NotYetImplemented
RAISERROR(” Not Yet Implemented Exception”, 18,1)
END’)
END
GO
ALTER PROCEDURE dbo.MyProc
@input int = 120
AS
BEGIN
DECLARE @output DATETIME
SELECT @output = DATEADD(MINUTE, -@input, GETDATE())
SELECT @output
END
GO
How about this : You make sure to delete it if exists before creating the procedure :
IF EXISTS (SELECT 1 FROM sys.procedures WHERE Name = ‘MyProc’)
DROP PROCEDURE MyProc
GO
CREATE PROCEDURE MyProc
@input int = 120
AS
BEGIN
DECLARE @timestamp DATETIME
SELECT @timestamp = DATEADD(MINUTE, -@input, GETDATE())
END
GO
The key thing to note is that “Alter” retains all security and property settings on the procedure. A Drop and Create will lose all security and properties. For instance, execute permission on a procedure may only be given to database admins. In a production environment it is far safer to alter than to create.
That would be an option but if you have granular permissions set on procedures those would be lost after the drop/create operation, that is why I rather using the if/create/alter approach.
Check if procedure exists then drop
Go
Create procedure
Create/alter procedure must be first statement in batch. You must use GO before create/alter, if your’e in batch of sql commands. Alternate way – dynamic sql, because of new session, and then your create/alter statement is first in the batch.
Correct @Juozas