As a developer working with SQL Server, you may encounter syntax errors while writing scripts to update your application database or modify stored procedures. These errors can be frustrating and time-consuming to troubleshoot. In this blog post, we will discuss a common syntax error and provide a solution/workaround to help you save time and energy.
One of our readers recently reached out to us with a syntax error they encountered while trying to alter a stored procedure. The error message they received was:
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”.
The reader shared their script, which included an IF statement to check if the procedure exists before either creating or altering it. However, they were encountering syntax errors within the IF clause. Here is a simplified version of their script:
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
After analyzing the script, we identified that the syntax errors were occurring due to the inability to alter the procedure inside the IF clause. To overcome this limitation, we suggested using dynamic SQL by enclosing the CREATE and ALTER statements under EXEC. Here is the modified version of the script:
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
By using dynamic SQL, the reader was able to successfully create or alter the stored procedure without encountering any syntax errors.
We hope this solution/workaround will help you overcome similar syntax errors in your SQL Server scripts. Remember to always double-check your syntax and consider using dynamic SQL when altering objects within conditional statements.
Thank you for reading and happy coding!