sql – 关键字’PROCEDURE’附近的语法不正确

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

在SQL Server 2008中执行后出错

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

解决方法

您将需要首先修复过程中的错误(为什么要尝试声明与参数同名的变量也使用SCOPE_IDENTITY()而不是@@ IDENTITY)但是类似于

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,@USER_ID      INTEGER OUTPUT)
    AS
        INSERT INTO [dbo].[IER_CandidateRegistration]
                    (User_Name,is_active)
        VALUES      (@UserName,@IsActiveUser)

        SELECT @USER_ID = SCOPE_IDENTITY()

RETURN 
    ')
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!