Whats the count???

  • twin.devil

    SSC-Insane

    Points: 22208

    Comments posted to this topic are about the item Whats the count???

  • This was removed by the editor as SPAM

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    Interesting question. Thank you.

  • Ed Wagner

    SSC Guru

    Points: 286957

    Good question. I use @@ROWCOUNT quite a bit and find it quite useful, but you have to follow the rules. Thanks for a good question.

  • RLilj33

    SSCrazy

    Points: 2146

    Thanks for the question. That could cause some issues if one isn't paying attention or properly testing their code.

  • Luis Cazares

    SSC Guru

    Points: 183531

    I couldn't find the explanation on why does the TRY...CATCH would reset the value of @@ROWCOUNT.

    From BOL

    Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

    There's no indication that TRY...CATCH will reset it, neither any of the control-of-flow statements.

    Does anyone knows if this is documented somewhere?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Ed Wagner

    SSC Guru

    Points: 286957

    Luis Cazares (10/9/2015)


    I couldn't find the explanation on why does the TRY...CATCH would reset the value of @@ROWCOUNT.

    From BOL

    Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

    There's no indication that TRY...CATCH will reset it, neither any of the control-of-flow statements.

    Does anyone knows if this is documented somewhere?

    I don't know for sure, but I wouldn't be surprised at all if it isn't documented in BOL.

  • Jacob Wilkins

    One Orange Chip

    Points: 27727

    Yeah, heavy reliance on the "Statements such as..." in the current documentation.

    The SQL Server 2000 documentation was a bit clearer, saying that @@ROWCOUNT is set to 0 by any statement that does not return rows. Ending the CATCH block doesn't return rows, so 0 it is.

    Par for the course on the current documentation, I suppose 🙂

  • TomThomson

    SSC Guru

    Points: 104762

    Nice question.

    It's one of those areas where the language is an inconsistently designed mess.

    Consistent language design would mean that END would always have the same effect, and that the BREAK of a WHILE should have the same effect as a caught error in a CATCH. Neither is the case in T-SQL.

    This is the situation we have (at least in sqls 2008 R2 it was - I haven't checked since then, and I'm sure it isn't documented):

    the END of a CATCH block sets @@rowcount to 0

    the END of a TRY block sets @@rowcount to 0

    the END of an ELSE block doesn't change @@rowcount

    the END of an IF block doesn't change @@rowcount

    the END of a WHILE block sets @@rowcount to 0 (hence you can't tell what the CONTINUE of a while block does to @@rowcount)

    the BREAK of a WHILE block leaves @@rowcount unchanged

    the END of a try block sets @@rowcount to 0 even if the catch block is entered (so error in try is not like break in while).

    Tom

  • RubySWorld

    SSCommitted

    Points: 1727

    I knew the answer but still executed it on my system. It's a really good developer question.

  • Koen Verbeeck

    SSC Guru

    Points: 258905

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • John Hanrahan

    Hall of Fame

    Points: 3825

    Great question.

  • twin.devil

    SSC-Insane

    Points: 22208

    Thanks guys. As Tom said this is really inconsistently designed.

    It's one of those areas where the language is an inconsistently designed mess.

    Consistent language design would mean that END would always have the same effect, and that the BREAK of a WHILE should have the same effect as a caught error in a CATCH. Neither is the case in T-SQL.

    This is the situation we have (at least in sqls 2008 R2 it was - I haven't checked since then, and I'm sure it isn't documented):

    the END of a CATCH block sets @@rowcount to 0

    the END of a TRY block sets @@rowcount to 0

    the END of an ELSE block doesn't change @@rowcount

    the END of an IF block doesn't change @@rowcount

    the END of a WHILE block sets @@rowcount to 0 (hence you can't tell what the CONTINUE of a while block does to @@rowcount)

    the BREAK of a WHILE block leaves @@rowcount unchanged

    the END of a try block sets @@rowcount to 0 even if the catch block is entered (so error in try is not like break in while).

    Tom

    If you are used to work in IF Clause and wanted to use TRY/Catch Clause in your T-SQL code you have be very careful and proper testing. thanks tom for the details this would help everybody.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Yep, you always have to watch out for this.

  • patricklambin

    SSCrazy Eights

    Points: 9964

    I knew the good answer only because a friend of mine had to face this question in an interview 2 years ago. Like him , I gave the wrong answer ( 10 ) , it is why I have been able to give the good answer. End of the story : my friend got the job , maybe because he dared to say before giving his reply that the script was written in an ugly way and that he would have written the code as

    DECLARE @mycount AS int = 0;

    BEGIN TRY

    SELECT TOP 10 * FROM sys.all_objects;

    SET @mycount = @@ROWCOUNT;

    END TRY

    BEGIN CATCH

    SELECT TOP 5 * FROM sys.all_objects;

    SET @mycount = @@ROWCOUNT;

    END CATCH

    SELECT @mycount AS ThatMyRowCount;

    The interviewer laughed and accepted 10 as answer ( a clever and comprehensive man , a rare value nowadays ... )

Viewing 15 posts - 1 through 15 (of 15 total)

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