Rowcount and multiple assignment

  • Nice question. It made me think and the explanation was good too.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good question. Made me realize that I need to study @@rowcount a bit more.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Great question. I got it wrong - I correctly followed the logic of the script but had too little understanding of how ROWCOUNT gets set. Good stuff to know (and study!).

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • 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.


    Just because you're right doesn't mean everybody else is wrong.

  • Well, I thought I'd @@ROWCOUNT in the bag until this came along 😛

    Nice question.

  • 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:-D



    See, understand, learn, try, use efficient
    © Dr.Plch

  • I enjoyed working this one out, line by line 🙂

    Good little puzzle to start the day. Thanks!

  • ? ... 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."

  • 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!

  • 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

  • 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 :w00t: 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 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 :w00t: 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

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply