Stored Procedures - Do you SET NOCOUNT ON for production and NOCOUNT OFF off for development/debugging purposes?

You should use SET NOCOUNT ON for production and NOCOUNT OFF off for development/debugging purposes (i.e. when you want the rowcounts to display as the messages from your T-SQL).

According to SQL Server Books Online: "For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced."

Example: Procedure that returns a scalar value (ClientID generated by an insert statement) should use OUTPUT keyword (not RETURN) to pass back data. This is how you should return a generated ClientID from the procedure, and also return a status value

CREATE PROCEDURE procClientInsert
/*
'---------------------------------------------- 
' Copyright 2001 SSW 
' www.ssw.com.au All Rights Reserved.
' VERSION AUTHOR DATE COMMENT 
' 1.0 DDK 17/12/2001 
'
'Calling example
'DECLARE @pintClientID int
'DECLARE @intReturnValue int
'exec @intReturnValue = procClientInsert 'TEST Entry', 
@pintClientID OUTPUT
'PRINT @pintClientID
'PRINT @intReturnValue
'---------------------------------------------- 
*/
@pstrCoName varchar (254),
@pintClientID int OUTPUT
AS
--IF ONE THING FAILS, ROLLBACK
SET XACT_ABORT ON
--THE COUNT WILL NOT NORMALLY DISPLAY IN AN APPLICATION IN PRODUCTION. 
--GET RID OF IT BECAUSE IT IS EXTRA TRAFFIC, AND CAN CAUSE 
PROBLEMS WITH SOME CLIENTS
SET NOCOUNT ON
--Generate a random number
SET @pintClientID = (SELECT CAST(RAND() * 100000000 AS int))
INSERT INTO Client (ClientID, CoName) VALUES (@pintClientID , 
@pstrCoName)
SET XACT_ABORT OFF
IF @@ROWCOUNT = 1 
 RETURN 0 -- SUCCESS
ELSE
 BEGIN
 IF @@ERROR=0 
 RETURN 1 -- FAILURE 
 ELSE
 RETURN @@ERROR -- FAILURE 
 END
SET NOCOUNT OFF

This procedure will display 0 or the error to indicate success or failure. You should base your actions on this return code.

This separates return values from actual data so that other programmers know what to expect.

Note:  If you are using SQL Server stored procedures to edit or delete data using a SqlDataAdapter, make sure that you do not use SET NOCOUNT ON in the stored procedure definition. This causes the rows affected count returned to be zero, which the DataAdapter interprets as a concurrency conflict. In this event, a DBConcurrencyException will be thrown.