CODEWITHSUNDEEP
c#sql-serversql-server-2008stored-proceduressql-server-2008-r2
Willy
Willy

Reputation: 10638

SQL Server Stored procedure returning an unexpected value

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

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?

Upvotes: 0

Views: 157

Answers (3)

PeterO
PeterO

Reputation: 171

The reason is that checking @@ROWCOUNT resets the value in @@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

Upvotes: 1

diiN__________
diiN__________

Reputation: 7656

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.

Upvotes: 1

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

 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.

Upvotes: 2

Related Questions