SQL Server 存储过程中的返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9837481/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return value in SQL Server stored procedure
提问by andrew slaughter
I have a stored procedure that has an if statement in it. If the number of rows counted is greater than 0 then it should set the only output parameter @UserIdto 0
我有一个存储过程，其中包含一个 if 语句。如果计数的行数大于 0，则应将唯一的输出参数设置@UserId为 0
However it only returns a value in the second part of the query.
但是它只在查询的第二部分返回一个值。
@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
IF
(SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress) > 0
BEGIN
SET @UserId = 0
END
ELSE
BEGIN
INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
SELECT SCOPE_IDENTITY()
END
END
回答by Curt
You can either do 1 of the following:
您可以执行以下操作之一：
Change:
改变：
SET @UserId= 0 to SELECT @UserId
SET @UserId= 0 到 SELECT @UserId
This will return the value in the same way your 2nd part of the IF statement is.
这将以与 IF 语句的第二部分相同的方式返回值。
Or, seeing as @UserId is set as an Output, change:
或者，看到 @UserId 设置为输出，更改：
SELECT SCOPE_IDENTITY()to SET @UserId = SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()到 SET @UserId = SCOPE_IDENTITY()
It depends on how you want to access the data afterwards. If you want the value to be in your result set, use SELECT. If you want to access the new value of the @UserId parameter afterwards, then use SET @UserId
这取决于您之后希望如何访问数据。如果您希望该值出现在您的结果集中，请使用SELECT. 如果以后要访问@UserId 参数的新值，请使用SET @UserId
Seeing as you're accepting the 2nd condition as correct, the query you could write (without having to change anything outside of this query) is:
看到您接受第二个条件是正确的，您可以编写的查询（无需更改此查询之外的任何内容）是：
@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
IF
(SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress) > 0
BEGIN
SELECT 0
END
ELSE
BEGIN
INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
SELECT SCOPE_IDENTITY()
END
END
回答by Arbejdsgl?de
I can recommend make pre-init of future index value, this is very usefull in a lot of case like multi work, some export e.t.c.
我可以推荐对未来的索引值进行预初始化，这在很多情况下非常有用，比如多工作，一些导出等
just create additional User_Seqtable:
with two fields: id Uniq indexand SeqVal nvarchar(1)
只需创建附加User_Seq表：有两个字段：id Uniq index和SeqVal nvarchar(1)
and create next SP, and generated ID value from this SP and put to new User row!
并创建下一个 SP，并从此 SP 生成 ID 值并放入新的用户行！
CREATE procedure [dbo].[User_NextValue]
as
begin
set NOCOUNT ON
declare @existingId int = (select isnull(max(UserId)+1, 0) from dbo.User)
insert into User_Seq (SeqVal) values ('a')
declare @NewSeqValue int = scope_identity()
if @existingId > @NewSeqValue
begin
set identity_insert User_Seq on
insert into User_Seq (SeqID) values (@existingId)
set @NewSeqValue = scope_identity()
end
delete from User_Seq WITH (READPAST)
return @NewSeqValue
end
回答by Andrey Gurinov
Try to call your proc in this way:
尝试以这种方式调用您的 proc：
DECLARE @UserIDout int
EXEC YOURPROC @EmailAddress = 'sdfds', @NickName = 'sdfdsfs', ..., @UserId = @UserIDout OUTPUT
SELECT @UserIDout
回答by KEERTHANA
@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
DECLARE @AA INT
SET @AA=(SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress)
IF @AA> 0
BEGIN
SET @UserId = 0
END
ELSE
BEGIN
INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
SELECT SCOPE_IDENTITY()
END
END
