SQL Server 2000 Stored Procedure - SET NOCOUNT ON

[kwilliams]

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.

[russellsoft]

SET NOCOUNT ON/OFF is using for disabling messages about counting of affected rows in select, insert, update or delete queries... So you must put it before one of this queries which you want to disable rows count message in...
And don't forget to return in the prevoius state... in the end of the stored procedure I advice you to put 'SET NOCOUNT OFF'...

[kwilliams]

Great! Thanks for the info russelsoft