Published on

November 5, 2016

Understanding SQL Server Syntax Errors

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!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.