THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser: @@ROWCOUNT

Without running this what do you think will be printed?

SET ROWCOUNT 0
DECLARE
@ int
SET
@ =6
IF @@ROWCOUNT = 1
    PRINT 'yes'
ELSE
    PRINT 'no'
PRINT @@rowcount

Published Thursday, May 08, 2008 11:50 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Saggi Neumann said:

Hey Denis,

If a variable can go unnamed (never tried that), you'd print "yes" (@@rowcount after set is 1), and the next @@rowcount would be 0 because print doesn't change @@rowcount value, and @@rowcount is zeroed out every time its fetched...

going to check it now... :)

May 8, 2008 1:16 PM
 

Nick said:

I didn't notice the unnamed variable, just converted it to 'some variable' in my head as I read. Otherwise @@ROWCOUNT behaved as expected. Very interesting.

May 8, 2008 11:32 PM
 

Hugo Kornelis said:

Saggi: The reason the second PRINT prints 0 is not that @@ROWCOUNT is zeroed out after being queried (it's not - try the snippet below), but because the PRINT statement executed inside the IF sets @@ROWCOUNT to 0. All PRINT statements set @@ROWCOUNT to 0.

SELECT '1 row';

SELECT @@ROWCOUNT, @@ROWCOUNT;

PRINT 'Set rowcount to zero';

SELECT @@ROWCOUNT, @@ROWCOUNT;

May 9, 2008 3:02 AM
 

Denis Gobo said:

BTW, RAISERROR works the same

SET ROWCOUNT 0

DECLARE @ int

SET @ =6

IF @@ROWCOUNT = 1

   RAISERROR ('Yes', 10,1) WITH NOWAIT;

ELSE

   RAISERROR ('No', 10,1) WITH NOWAIT;

PRINT @@rowcount

GO

May 9, 2008 11:13 AM
 

Saggi Neumann said:

Hugo - How can you be sure that the print statement sets the @@Rowcount to zero? (Unless it's mentioned somewhere in BOL or by the person who wrote it... :-) )

I'm not sure that your example proves your point, because we also use SELECT @@rowcount, @@error and if this SELECT works well, it doesn't reset @@error.

Also, this code shows that either the IF resets the @@rowcount to 0 (like print), or that it's reset after it's read...

SET ROWCOUNT 0

DECLARE @ int

SET @ =6

IF @@ROWCOUNT = 1

  select @@ROWCOUNT --result is 0

ELSE

  select @@ROWCOUNT

PRINT @@rowcount --result is 1

May 9, 2008 11:30 AM
 

Denis Gobo said:

SET ROWCOUNT 0

DECLARE @ int

SET @ =6

IF @@ROWCOUNT = 1

select @@ROWCOUNT --result is 0

GO

SET ROWCOUNT 0

DECLARE @ int

SET @ =6

select @@ROWCOUNT --result is 1

GO

SET ROWCOUNT 0

DECLARE @ int

SET @ =6

PRINT 'a'

select @@ROWCOUNT --result is 0

GO

May 9, 2008 12:18 PM
 

Denis Gobo said:

BTW, If anyone is interested how I come up with most of the teasers, it is when answering questions.

For example this teaser I got from this question

http://www.tek-tips.com/viewthread.cfm?qid=1472002&page=1

as you can see I had to spoon feed the answer to this person because the person either had no clue or was to lazy to type/try stuff out

May 9, 2008 2:25 PM
 

Saggi Neumann said:

Denis - that'll do... :) It also means that IF sets @@rowcount to zero as well.

The moral of the story is: You shouldn't use @@rowcount directly, because it won't have the same value next time you try to retrieve it. You'd rather immediately put the value in a variable (set @rowcount = @@rowcount) and use the variable. The same goes for @@error.

May 10, 2008 1:54 AM
 

Michael Zilberstein said:

Hello Saggi,

You can use @@ROWCOUNT (as well as @@ERROR) only immidiately after the statement you want to check error status or rowcount for. Every new statement resets both of those counters. One of the widespread errors is:

DECLARE @RowsAffected INT, @ErrorStatus INT

UPDATE sometable

SET @RowsAffected = @@ROWCOUNT

SET @ErrorStatus = @@ERROR

In this case @@ERROR will always be zero because it actually refers to SET @RowsAffected = @@ROWCOUNT statement and not to UPDATE.

May 12, 2008 12:39 PM
 

Denis Gobo said:

That is right the 'correct' way would be

SELECT @RowsAffected = @@ROWCOUNT, @ErrorStatus = @@ERROR

May 12, 2008 12:41 PM
 

AlexB said:

SET ROWCOUNT 0

DECLARE @a int

SET @a =6

IF @@ROWCOUNT = 1

   PRINT @@ROWCOUNT -- result is 0

ELSE

   PRINT 'no'

PRINT @@rowcount

May 14, 2008 9:53 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement