October 8, 2015 at 10:53 pm
Comments posted to this topic are about the item Whats the count???
October 9, 2015 at 12:42 am
This was removed by the editor as SPAM
October 9, 2015 at 2:43 am
Interesting question. Thank you.
October 9, 2015 at 5:08 am
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.
October 9, 2015 at 7:18 am
Thanks for the question. That could cause some issues if one isn't paying attention or properly testing their code.
October 9, 2015 at 7:39 am
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?
October 9, 2015 at 8:04 am
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.
October 9, 2015 at 8:27 am
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 🙂
October 11, 2015 at 6:09 am
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
October 11, 2015 at 6:46 am
I knew the answer but still executed it on my system. It's a really good developer question.
October 12, 2015 at 1:31 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2015 at 10:34 am
Great question.
October 14, 2015 at 12:27 am
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.
October 21, 2015 at 7:40 am
Yep, you always have to watch out for this.
November 7, 2015 at 5:01 am
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