• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1098
  • Last Modified:

set nocount scope

I'm having trouble understanding the scope of a command like SET NOCOUNT ON. I know I want to use this in stored procedures, but do I put this command in the sp's definition or outside the CREATE PROCEDURE command? Does running the command only last for one transaction?
0
elmoredaniel
Asked:
elmoredaniel
2 Solutions
 
ColosseoCommented:
Hi

yes you are right you put it inside the definition, so for example you might have

CREATE PROCEDURE get_Table1_p

AS

SET NOCOUNT ON

SELECT * FROM Table1

As for whether or not it only lasts for one transaction I am pretty sure that it is specific to the sp that it is included in.

I know that I use it in every sp I write as it does helps to reduce network traffic by not returning the number of rows affected line when it is not needed.

HTH

Scott
0
 
curtis591Commented:
It is a connection level setting, so it lasts while you have that connection to the server.  You could put it in either place I guess.  There is a connection default that you can set all connections that connect to your sql server.  Right click on your server in Enterprise Manager click properties and it is in there.  (It might be set on the database if is not in there but I am pretty sure it is on the server)  If you switch it for the connection I am not to sure what else it effects.  We have always just set it in the procedures.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now