Incorrect syntax near the keyword 'PROCEDURE' [closed]

Question

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'.

Answer 1

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.

Answer 2

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