在SQL Server存储过程中将@@rowcount分配给变量

I'm having trouble with a stored procedure that I wrote. If the select statement returns no values, I'd like the procedure to return -1. But, when the select statement returns results, I'd like to return the @@rowCount.

我写的存储过程有问题。如果select语句不返回值，我希望过程返回-1。但是，当select语句返回结果时，我希望返回@@rowCount。

When I'm running the stored procedure, it's returning -1 when there are no results but it's returning 1 when there are results.

当我运行存储过程时，它在没有结果时返回-1，但在有结果时返回1。

Any suggestions as to what I need to change?

关于我需要改变什么，有什么建议吗?

    -- Add the parameters for the stored procedure here
    @LoginName VARCHAR(25),
    @Password VARCHAR(10),
    @rowsAffected INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @PasswordSalt VARCHAR(36) 

    SELECT @PasswordSalt = (SELECT PasswordSalt FROM Employee WHERE LoginName = @LoginName)

    SELECT 
        Employee.ID, FirstName, LastName, LoginName, Email, StatusTypeID, 
        Description AS StatusDescription
    FROM
        Employee
    JOIN
        StatusType st ON Employee.StatusTypeID = st.ID
    WHERE
        LoginName = @LoginName 
        AND PasswordHash = HashBytes('SHA2_512', @Password + Cast(@PasswordSalt AS nvarchar(36))) 
        AND StatusTypeID = 1

    IF @@ROWCOUNT = 0
    BEGIN
        SET @rowsAffected = -1
    END
    ELSE
    BEGIN
        SELECT @rowsAffected = @@RowCount   
    END
END

1 个解决方案

#1

Your if statement is probably resetting @@rowcount.

您的if语句可能正在重置@@rowcount。

First store the value, then test it:

首先存储值，然后测试它:

select ...
from   ...

set @rowsAffected = @@rowcount
if @rowsAffected = 0
    set @rowsAffected = -1

#1