Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
benildusmuerling
Yak Posting Veteran
81 Posts |
 Posted - 2012-07-12 : 20:32:00
|
| Dear Excellent Guys,Thanks for the time you have taken in reading my posts in the past, would like to ask about why do you put SET NOCOUNT ON. I used to see in the stored procedures this had been added more often.I would like to ask is it a must to put a SET NOCOUNT OFF, had there been a SET NOCOUNT ON.Thanks,Antonio |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-12 : 20:34:37
|
| That simply control whether or not you see the message that tells you how many rows were returned/affected by a statement. If you don't want that output to show for each statement then SET NOCOUNT ON.Be One with the OptimizerTG |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-12 : 21:22:59
|
| I have had the experience where some clients would interpret the "rows updated" messages as part of the record set and would therefore choke.I don't quite recall which clients they were though - Perl using Active State Perl or Java if I remember correctly. Regardless, since it causes a problem for at least some clients, not a bad idea to use that in the stored procs. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-12 : 22:13:20
|
| True - it's been awhile since that caused any problems for the places I've worked (all MS .net) but I do recall that.>>I would like to ask is it a must to put a SET NOCOUNT OFF, had there been a SET NOCOUNT ON.No, not a must. To Sunitabeck's point - safer to have it ON if you're not sure. But that setting is only in scope of the procedure that it is SET in. So doesn't matter what you do at the end of the procedure.Be One with the OptimizerTG |
|
|
|
|
|
|
SET NOCOUNT ON and SET NOCOUNT OFF