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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-28 : 08:00:15
|
| Oren writes "when to use SET NOCOUNT OFF/ON in stored procedures?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-28 : 08:00:36
|
| Always, put SET NOCOUNT ON right at the top. |
 |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-10-28 : 08:17:45
|
| Its a good practice to put "SET NOCOUNT ON" on the top of stored procedure and "SET NOCOUNT OFF" at the end. But only use when you are not using the counts. Let me explain this. If in your application you are trying to check how many rows were affected or retrived by reading the counts then don't use SET NOCOUNT ON. If you do that you will get nothing in your application. Its a good practice to use set nocount on. The counts which are not needed in most of the cases are removed and not sent to the client thus decreases data which is sent to the client. RegardsSachin Samuel |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2005-10-28 : 09:09:06
|
| As Rob said, you should *always* use SET NOCOUNT ON. Not only does it suppress this xxxrow(s) affected message, but it also does suppress the DONE_IN_PROC message, that SQL Server sends by default after each statement. This way you can avoid unnecessary traffic between client and server. You might find this interesting:http://support.microsoft.com/kb/240882/en-ushttp://support.microsoft.com/kb/195491/en-us--Frank KalisMicrosoft SQL Server MVPhttp://www.insidesql.deHeute schon geblogged? http://www.insidesql.de/blogsIch unterstütze PASS Deutschland e.V. (http://www.sqlpass.de) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-28 : 09:09:09
|
| General approachSET NOCOUNT ON --Your codeSET NOCOUNT OFFMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
SET NOCOUNT