在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
在sql Server 2008中执行后出错
Msg 156,Level 15,State 1,Line 3
Incorrect Syntax near the keyword ‘PROCEDURE’.
解决方法
您可以在子批处理中运行CREATE,该批处理仅在不是EXISTS的情况下编译和执行.
您将需要首先修复过程中的错误(为什么要尝试声明与参数同名的变量也使用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
