CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
user2323308
user2323308

Reputation: 769

Incorrect syntax near the keyword 'PROCEDURE'

Execute following statement in the SQL Server

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CandidateRegistration]') AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[SP_CandidateRegistration]
    (
            @UserName VARCHAR(50),      
            @Password VARCHAR(50),
            @EmailID VARCHAR(50),
            @TestId int,
            @IsActiveUser INTEGER,      
            @USER_ID INTEGER OUTPUT
    )
    AS
    DECLARE @UserName VARCHAR(50)
    DECLARE @Password VARCHAR(50)
    DECLARE @EmailID VARCHAR(50)
    DECLARE @TestId int
    DECLARE @IsActiveUser INTEGER       
    DECLARE @USER_ID INTEGER 

    INSERT INTO [dbo].[IER_CandidateRegistration](User_Name, Password, EmailId, Test_Id, is_active )
    VALUES (@UserName, @Password, @EmailID,@TestId, @IsActiveUser) 
    select @USER_ID=@@identity
    RETURN
END
GO

Error after executing in SQL Server 2008

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

Upvotes: 8

Views: 19641

Answers (3)

Martin Smith
Martin Smith

Reputation: 452947

You can run the CREATE in a child batch that is only compiled and executed IF NOT EXISTS.

You will need to fix the errors in the procedure first (why are you trying to declare variables with the same name as the parameters also use SCOPE_IDENTITY() not @@IDENTITY) but something like

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CandidateRegistration]') AND type in (N'P', N'PC'))
BEGIN
EXEC('
     CREATE PROCEDURE [dbo].[SP_CandidateRegistration] (@UserName     VARCHAR(50),
                                                       @Password     VARCHAR(50),
                                                       @EmailID      VARCHAR(50),
                                                       @TestId       INT,
                                                       @IsActiveUser INTEGER,
                                                       @USER_ID      INTEGER OUTPUT)
    AS
        INSERT INTO [dbo].[IER_CandidateRegistration]
                    (User_Name,
                     Password,
                     EmailId,
                     Test_Id,
                     is_active)
        VALUES      (@UserName,
                     @Password,
                     @EmailID,
                     @TestId,
                     @IsActiveUser)

        SELECT @USER_ID = SCOPE_IDENTITY()

RETURN 
    ')
END

NB: This question was asked in 2013 (about SQL Server 2008) but since SQL Server 2016 it has been possible to do CREATE OR ALTER PROCEDURE to avoid the need for any IF NOT EXISTS type procedural code at all.

Upvotes: 10

AnandPhadke
AnandPhadke

Reputation: 13486

Try this:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CandidateRegistration]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_CandidateRegistration]
GO
CREATE PROCEDURE [dbo].[SP_CandidateRegistration]
    (
            @UserName VARCHAR(50),      
            @Password VARCHAR(50),
            @EmailID VARCHAR(50),
            @TestId int,
            @IsActiveUser INTEGER,      
            @USER_ID INTEGER OUTPUT
    )
    AS
    BEGIN
    DECLARE @UserName1 VARCHAR(50)
    DECLARE @Password1 VARCHAR(50)
    DECLARE @EmailID1 VARCHAR(50)
    DECLARE @TestId1 int
    DECLARE @IsActiveUser1 INTEGER       
    DECLARE @USER_ID1 INTEGER 

    INSERT INTO [dbo].[IER_CandidateRegistration](User_Name, Password, EmailId, Test_Id, is_active )
    VALUES (@UserName, @Password, @EmailID,@TestId, @IsActiveUser) 
    select @USER_ID=@@identity
    RETURN
END
GO

Upvotes: 1

Pranav
Pranav

Reputation: 8871

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch. So,You have to do like this:-

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_CandidateRegistration]') AND type in (N'P', N'PC'))
DROP PROCEDURE [SP_CandidateRegistration]
GO

CREATE PROCEDURE [dbo].[SP_CandidateRegistration]
    (
            @UserName VARCHAR(50),      
            @Password VARCHAR(50),
            @EmailID VARCHAR(50),
            @TestId int,
            @IsActiveUser INTEGER,      
            @USER_ID INTEGER OUTPUT
    )
    AS

    INSERT INTO [dbo].[IER_CandidateRegistration](User_Name, Password, EmailId, Test_Id, is_active )
    VALUES (@UserName, @Password, @EmailID,@TestId, @IsActiveUser) 
    select @USER_ID=@@identity
    RETURN

GO

Also, you are again declaring the variables.

Upvotes: 10

Related Questions