Whats the count???

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

  • This was removed by the editor as SPAM

  • Interesting question. Thank you.

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

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

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

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

  • 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

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

  • Great question, thanks.

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

  • Great question.

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

  • Yep, you always have to watch out for this.

  • 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 14 (of 14 total)

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