SQL SERVER – FIX: Incorrect Syntax Near the Keyword ‘PROCEDURE’

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.

SQL SERVER - FIX: Incorrect Syntax Near the Keyword 'PROCEDURE' syntax-error-01-800x580

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)

Previous Post
SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine?
Next Post
SQL SERVER 2016 – DBCC Command Enhancement with MAXDOP

Related Posts

No results found.

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())
    /**********************************************************/

    Reply
  • It’s working fine in Sql Server 2008

    Reply
  • 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

    Reply
  • 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

    Reply
    • Philip Leitch
      March 5, 2018 6:40 am

      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.

      Reply
  • Michael Villegas Cuellar
    November 20, 2016 8:14 pm

    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.

    Reply
  • Check if procedure exists then drop
    Go
    Create procedure

    Reply
  • Juozas Alševskis
    December 29, 2016 9:22 pm

    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.

    Reply

Leave a Reply