CODEWITHSUNDEEP
sqlsql-server
Tony M.
Tony M.

Reputation: 9

Why is the @@ROWCOUNT variable returning zero after the IF statement

This is a simple code referring to the pubs database. The SELECT statement returns 10 records so @@ROWCOUNT variable should be set to 10. But how come in the message window, it says '0 records found'. Is there a reason why after an IF statement @@ROWCOUNT is set to 0?

If I put SELECT @@ROWCOUNT right after the WHERE statement, the @@ROWCOUNT variable is at 10. But it changes after executing the IF STATEMENT.

SELECT *
FROM pubs.dbo.employee
WHERE pub_id ='0877' 

IF   @@ROWCOUNT > 0
     PRINT CONVERT(CHAR(2), @@ROWCOUNT) + ' records found'
ELSE
    PRINT 'No records found'

Upvotes: 0

Views: 947

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

@@ROWCOUNT returns the row count for the last statement. It is highly volatile. So, basically, anything can reset it.

If you care about it, assign it to a parameter immediately!

DECLARE @ROWCNT INT;

SELECT * FROM pubs.dbo.employee WHERE pub_id = '0877';
SET @ROWCNT = @@ROWCOUNT;

Then use the parameter value.

Upvotes: 3

Related Questions