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?
elmoredanielAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.