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 8, 2008 11:50 AM by Denis Gobo
Filed under:

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
New Comments to this post are disabled

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

Privacy Statement