SQL Server 2000 Stored Procedure - SET NOCOUNT ON

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server 2000 Stored Procedure - SET NOCOUNT ON

Author

Topic

kwilliams

194 Posts

Posted - 2006-04-06 : 16:08:58

I'm a complete newbie to SQL Server stored procedures (SP), and I have a simple question about SET NOCOUNT. I want to add SET NOCOUNT ON to each SP to speed up the performance a bit, but I'm not sure where to place it. Is there any uniform place to put it within a stored procedure, because I've seen it placed at the beginning, middle, and end of a SP statement. I'm figuring from that that it doesn't matter where it's placed, but I wanted to make sure. This is how I have it placed currently:

CREATE PROCEDURE [dbo].[spTitle]
@ID int,
@Col1 varchar (30),
@Col2 varchar (30),
@Col3 varchar (30),
AS

SET NOCOUNT ON

UPDATE tblTitle
SET
Col1 = @Col1,
Col2 = @Col2,
Col3 = @Col3,
WHERE ID = @ID
GO

Thanks for any help.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-06 : 16:29:58

Any statements that execute before the SET NOCOUNT will still generate row counts, so you're best off with it where it is.

Mark

kwilliams

194 Posts

Posted - 2006-04-06 : 16:35:44

So then it wouldn't matter if I put it at the beginning or kept it where it's at, as long as it's not after the statement executes. Am I correct?

P.S. Thanks for your quick response.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 17:15:36

When you say "at the beginning" do you mean before the CREATE PROCEDURE statement? Then the answer is no, you want it right where your example is so that it is inside the procedure code. By the way, I'd suggest putting a SET NOCOUNT OFF before the GO at the end just to keep the cycle explicit.

---------------------------
EmeraldCityDomains.com

kwilliams

194 Posts

Posted - 2006-04-06 : 17:20:19

quote:
When you say "at the beginning" do you mean before the CREATE PROCEDURE statement?

Yes, that's what I meant. Sorry for the confusion. As I stated, I'm very new to stored procedures, and obviously also to the proper terminology.

quote:
Then the answer is no, you want it right where your example is so that it is inside the procedure code. By the way, I'd suggest putting a SET NOCOUNT OFF before the GO at the end just to keep the cycle explicit.

So the best way for it to be set up is like this:
CREATE PROCEDURE [dbo].[spTitle]
@ID int,
@Col1 varchar (30),
@Col2 varchar (30),
@Col3 varchar (30),
AS

SET NOCOUNT ON

UPDATE tblTitle
SET
Col1 = @Col1,
Col2 = @Col2,
Col3 = @Col3,
WHERE ID = @ID

SET NOCOUNT OFF

GO

...and that will accomplish what I want. Correct?

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 17:23:47

Yep, looks good to me, although I don't know how much performance difference you'll see. If that's your main concern, I'd look at indexes. But setting NOCOUNT can help clear up issues that other code sometimes had.

---------------------------
EmeraldCityDomains.com

kwilliams

194 Posts

Posted - 2006-04-06 : 17:28:25

Will do. Thanks again for all of your help:)

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources