SQL Server存储过程返回意外值

I have this stored procedure in SQL Server 2008 that is called from C# code:

我在SQL Server 2008中有这个从C＃代码调用的存储过程：

CREATE PROCEDURE [dbo].[DepartmentStoreControlAccess]              
    @DeptId INT,             
    @intError tinyint OUTPUT,
AS
    SET @intError = 0

    SELECT @AccessTime = Hour
    FROM Entries
    WHERE DeptId = @DeptId

    IF @@ROWCOUNT = 1
    BEGIN
        -- DO STUFF and update @intError
    END
    ELSE
    BEGIN
        IF @@ROWCOUNT = 0   
            SET @intError = 0
        ELSE
            SET @intError = -1 
    END

There is a case I know for sure that select:

有一个案例我肯定知道选择：

SELECT @AccessTime = Hour
FROM Entries
WHERE DeptId = @DeptId

could return more than 1 row, let's say 2, 3, 4, .... , N so output parameter @intError should be returned to -1 in this case according to my stored procedure but I do not know why, the stored procedure in this case (when select returns more than 1 row) is returning 0 in the output parameter @intError. Why?

可以返回超过1行，让我们说2,3,4，....，N所以输出参数@intError应该返回-1在这种情况下根据我的存储过程但我不知道为什么，存储过程在这种情况下（当select返回多于1行时）在输出参数@intError中返回0。为什么？

3 个解决方案

#1

You need to save the @@ROWCOUNT into a variable when you first access it the first time because @@ROWCOUNT refers to the last statement. In your ELSE case, @@ROWCOUNT returns 0 because your last statement was the @@ROWCOUNT in your IF statement.

当您第一次访问变量时，需要将@@ ROWCOUNT保存到变量中，因为@@ ROWCOUNT指的是最后一个语句。在您的ELSE情况下，@@ ROWCOUNT返回0，因为您的上一个语句是IF语句中的@@ ROWCOUNT。

#2

 DECLARE @Count INT
 SELECT @AccessTime=Hour
 FROM   Entries                                                                       
 WHERE  DeptId=@DeptId
 SET @Count = @@ROWCOUNT

And use variable

并使用变量

Returns the number of rows affected by the last statement.

返回最后一个语句影响的行数。

#3

The reason is that checking @@ROWCOUNT resets the value in @@ROWCOUNT

原因是检查@@ ROWCOUNT会重置@@ ROWCOUNT中的值

IF @@ROWCOUNT = 1   -- From this point @@ROWCOUNT = 0
BEGIN
    -- DO STUFF and update @intError
END
ELSE
BEGIN
    if @@ROWCOUNT = 0   -- So we always end up here
        set @intError = 0
    else
        set @intError = -1 
END

#1

#2

 DECLARE @Count INT
 SELECT @AccessTime=Hour
 FROM   Entries                                                                       
 WHERE  DeptId=@DeptId
 SET @Count = @@ROWCOUNT