Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Rowcount and multiple assignment Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 2:49 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:37 AM
Points: 513, Visits: 429
Well, I thought I'd @@ROWCOUNT in the bag until this came along
Nice question.
Post #903593
Posted Thursday, April 15, 2010 1:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 1,298, Visits: 1,306
rjv_rnjn (4/14/2010)
Well, I thought I'd @@ROWCOUNT in the bag until this came along
Nice question.

Everytime I thought I'd something in the bag I realized I have overlooked some detail. Very often it's a very important detail




See, understand, learn, try, use efficient
© Dr.Plch
Post #903807
Posted Thursday, April 15, 2010 1:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:26 AM
Points: 1,140, Visits: 325
I enjoyed working this one out, line by line :)

Good little puzzle to start the day. Thanks!
Post #903826
Posted Thursday, April 15, 2010 1:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:26 AM
Points: 1,140, Visits: 325
? ... from BOL ...

"Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'."

... and ..

"Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0."
Post #903827
Posted Friday, April 16, 2010 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:04 PM
Points: 40, Visits: 71
I spend 30 precious minutes every Friday reading my SQL Server Central articles and attempting the QotD. This is the first time I cheated (IMHO) and ran the code for an answer. Like other people, the 111 was a surprise to me and is highly useful esoteric knowledge for future use... In short - excellent QotD!
Post #904945
Posted Saturday, April 17, 2010 5:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
Rune Bivrin (4/14/2010)
It's important to note that SET @result = cast (@@rowcount as varchar) does NOT change @@ROWCOUNT. The first 1 comes from the last INSERT #n VALUES(3).

This is one of the important differences between SELECT and SET when assigning variables. SET never yields a rowcount, and thus doesn't change @@ROWCOUNT.

Someone may already have said this, but in case they haven't:

That is very wrong (but it doesn't make a difference to the answer in this case because a SET @localvariable statement sets @@rowcount to 1).

From BoL (http://msdn.microsoft.com/en-gb/library/ms187316.aspx):
Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.


This can be demonstrated by a small adaptation to the code of the question:
declare @result varchar (5)
create table #n (n int)
insert #n values (1),(2),(2)
set @result = cast (@@rowcount as varchar)
/*SET NOCOUNT OFF*/
select @result = @result + cast (@@rowcount as varchar) from #n
select @result + cast (@@rowcount as varchar)
drop table #n

which will deliver 31113, not 33333 as would be the case if SET did not set @@rowcount.


Tom
Post #905384
Posted Sunday, April 18, 2010 5:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 11,168, Visits: 10,930
Tom.Thomson (4/17/2010)
Someone may already have said this, but in case they haven't:

Actually, pretty much every other post so far has covered it and Rune responded:
"Yep, I was wrong about that. And I even ran a test to verify my thought before I posted, but I managed to read the results of that test wrong. My bad. I suppose that was because I expected it to work that way simply based on the fact that SET doesn't yield a 1 row(s) affected." Ah, well. Learn something new everyday.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #905552
Posted Sunday, April 18, 2010 9:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
Paul White NZ (4/18/2010)
Tom.Thomson (4/17/2010)
Someone may already have said this, but in case they haven't:

Actually, pretty much every other post so far has covered it and Rune responded:
"Yep, I was wrong about that. And I even ran a test to verify my thought before I posted, but I managed to read the results of that test wrong. My bad. I suppose that was because I expected it to work that way simply based on the fact that SET doesn't yield a 1 row(s) affected." Ah, well. Learn something new everyday.

Yes, if I had read all the posts before firing up the editor I wouldn't have bothered.


Tom
Post #905568
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse