Syntax error in SQL Server stored procedure creation

Question

I want to create a stored procedure..so before creating it I'm checking if the database and the table exist and then create the stored procedure. I'm using the below SQL script, but it throws syntax error. I have no clue why it is failing.

This is the error I get:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'PROCEDURE'.

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ';'.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.

Code:

IF (EXISTS(SELECT TOP 1 * FROM sys.databases
           WHERE name = 'testdb1'))
BEGIN
    IF (EXISTS (SELECT TOP 1 *
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_SCHEMA = 'dbo' 
                  AND TABLE_NAME = 'TableA'))
    BEGIN 
        CREATE PROCEDURE SP_TEST
        AS
        BEGIN
            SET NOCOUNT ON;

            SELECT TableA.[Key] AS Expr1
            FROM testdb1.dbo.TableA
            ORDER BY TableA.[Key];
        END;    
        GO
    END
END

Subha · Accepted Answer · 2014-09-10 07:52:49Z

1

This is because of the ';' and 'GO'.

Use the script below -

IF (EXISTS(SELECT top 1 * FROM sys.databases
            where name = 'testdb1'))
            BEGIN
                IF (EXISTS (SELECT top 1 *
                                 FROM INFORMATION_SCHEMA.TABLES 
                                 WHERE TABLE_SCHEMA = 'dbo' 
                                 AND  TABLE_NAME = 'TableA'))
                BEGIN 
                    CREATE PROCEDURE SP_TEST
                    AS
                    BEGIN
                        SET NOCOUNT ON;
                        SELECT TableA.[Key] AS Expr1
                        FROM testdb1.dbo.TableA
                        ORDER BY TableA.[Key]
                    END
                END
            END

answered Sep 10, 2014 at 7:52

Subha

1,0511 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jayaraman Over a year ago

I'm still getting the error Incorrect syntax near the keyword 'PROCEDURE'.the other 2 issues are resolved

Jayaraman · Accepted Answer · 2014-09-10 10:18:43Z

0

adding these lines of code before Create POrocedure SP_test resolved the issue.

                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO

answered Sep 10, 2014 at 10:18

Jayaraman

1472 silver badges11 bronze badges

Collectives™ on Stack Overflow

Syntax error in SQL Server stored procedure creation

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related