SQL Server 存储过程中的返回值 [英] Return value in SQL Server stored procedure

问题描述

我有一个包含 if 语句的存储过程.如果计数的行数大于 0，则应将唯一的输出参数 @UserId 设置为 0

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 @UserId to 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

推荐答案

您可以执行以下任一操作:

You can either do 1 of the following:

变化:

SET @UserId = 0 到 SELECT @UserId

这将以与 IF 语句的第二部分相同的方式返回值.

This will return the value in the same way your 2nd part of the IF statement is.

或者，看到@UserId设置为输出，更改:

Or, seeing as @UserId is set as an Output, change:

SELECT SCOPE_IDENTITY() 到 SET @UserId = SCOPE_IDENTITY()

这取决于您之后希望如何访问数据.如果您希望该值出现在您的结果集中，请使用 SELECT.如果以后要访问@UserId 参数的新值，请使用SET @UserId

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

看到您接受第二个条件是正确的，您可以编写的查询(无需更改此查询之外的任何内容)是:

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

这篇关于SQL Server 存储过程中的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！