Simple SQL Being All Weird (To My Knowledge At Least)

Question

I have this really simple piece of SQL that's misbehaving and for the life of me can't figure out why: Note: It's actually within a stored procedure but this simplified version of it demonstrates the problem area.

declare @UserId int = 297;
declare @Password [RSNAME] = 'somepassword99';

begin try
if (@UserId > 0 and @Password <> '')
  begin
    update [User]
       set [Password] = @Password
     where UserId = @UserId;
    print(@@rowcount); --just to track changes
  end
end try
begin catch
  raiserror ('Error updating password!', 16, 1);
end catch;

if (@@rowcount <> 1)
begin
  print(@@rowcount); --just to track changes
  raiserror ('Error updating password! Zero or too many rows changed!', 16, 1);
end

On execution, I get this result:

(1 row(s) affected)

1 (this is the rowcount inside the if condition)

0 (this is the rowcount outside the if condition)

Msg 50000, Level 16, State 1, Line 20

Error updating password! Zero or too many rows changed!

If I were to place "if (@@rowcount <> 1)" section within the begin catch section, then all works fine. @@rowcount seems to be reset after altering the table?

`@@ROWCOUNT` is reset by *every* statement. – Damien_The_Unbeliever Oct 20 '15 at 08:48 — Damien_The_Unbeliever, Oct 20 '15 at 08:48

Lukasz Szozda · Accepted Answer · 2015-10-20T08:52:33.000

@@ROWCOUNT is reset by each statement. You need to store @@rowcount in helper variable:

declare @UserId int = 297;
declare @Password [RSNAME] = 'somepassword99';
declare @rowcount INT = 0;

begin try
if (@UserId > 0 and @Password <> '')
  begin
    update [User]
       set [Password] = @Password
     where UserId = @UserId;

     select @rowcount = @@rowcount;
    print(@rowcount); --just to track changes
  end
end try
begin catch
  raiserror ('Error updating password!', 16, 1);
end catch;

if (@rowcount <> 1)
begin
  print(@rowcount); --just to track changes
  raiserror ('Error updating password! Zero or too many rows changed!', 16, 1);
end

Simple demo:

SELECT 1
UNION ALL
SELECT 2;

SELECT @@rowcount; -- 2

SELECT @@rowcount; -- 1

and:

SELECT 1
UNION ALL
SELECT 2;

PRINT(@@rowcount); -- Print 2

SELECT @@rowcount; -- 0

Simple SQL Being All Weird (To My Knowledge At Least)

1 Answers1