1

I have the following stored procedure:

ALTER PROCEDURE [dbo].[spTitle_GetTitleById]
(
   @TitleId INT
)

AS

BEGIN

   SET NOCOUNT ON;

   SELECT
      Id,
      Name,
      Active
   FROM
      Title
   WHERE
      Id = @TitleId

END

I was told to use SET NOCOUNT ON; if I don't want messages to be returned. I ran this stored procedure through SQL Server Management Studio 2008 and I got the following message:

(1 row(s) affected)

This is still a message. One of our DBAs said that this will be the case, but when it is run through an application it will not return any messages. Is there a way that I can test to see if messages were returned or not when I use SET NOCOUNT ON; I don't want to assume, I want to know.

I right clicked the stored procedure and selected Execute Stored Procedure... I then set it to OFF, and I got:

(1 row(s) affected)
(1 row(s) affected)

So setting it to ON or OFF it still brought back messages in the Messages tab in the results panel.

Just another question, when will it be wise (in what scenarios) to use SET NOCOUNT OFF;?

Brendan Vogt
  • 25,678
  • 37
  • 146
  • 234
  • Sorry for a possibly stupid question, just to clarify: you ran the stored procedure itself (`EXEC spTitle_GetTitleById`) or the `ALTER PROCEDURE` statement? – Quassnoi May 04 '11 at 13:37
  • `SET NOCOUNT ON` is reset when the procedure exits and it goes up the call stack. Do you have any other statements either side of the stored procedure call? – Martin Smith May 04 '11 at 13:39
  • @Quassnoi: Not a dumb question. I right clicked it and selected to execute the stored procedure. – Brendan Vogt May 04 '11 at 16:45
  • @Martin: Nope it's just this single stored procedure. I right clicked it and selected to execute the stored procedure. – Brendan Vogt May 04 '11 at 16:45
  • But that generates the script as per my answer. The message is coming from the `SELECT 'Return Value'` statement. – Martin Smith May 04 '11 at 16:46

3 Answers3

3

SET NOCOUNT ON is reset when the procedure exits and it goes up the call stack. When you execute the procedure from SSMS it generates a script like the following.

DECLARE @return_value int

EXEC    @return_value = [dbo].[spTitle_GetTitleById]
        @TitleId = 1

SELECT  'Return Value' = @return_value  /*Message comes from here*/

If youi wanted to avoid that for some reason you would need to SET NOCOUNT ON in the outer batch. See SET NOCOUNT ON usage for some discussion about the merits of having this ON or OFF

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Yes you are correct. I tested it, and I commented out the SELECT part above and it just returned 'Command(s) completed successfully.'. Is this not considered a message? – Brendan Vogt May 04 '11 at 17:02
  • `set nocount on` [is only documented](http://msdn.microsoft.com/en-us/library/ms189837.aspx) as "Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set." it won't stop all messages. – Martin Smith May 04 '11 at 17:05
2

Just another question, when will it be wise (in what scenarios) to use SET NOCOUNT OFF?

See What are the advantages and disadvantages of turning NOCOUNT off in SQL Server queries? For the benefits turning SET NOCOUNT ON

As for why you would want to turn this off (so that rowcounts are returned) - you need this off whenever you want to be able to tell how many rows were affected in situations where there is no resultset, or you wish to be able to get a rowcount without first reading through the entire resultset.

For example in .Net the DataAdapter class uses rowcounts and so setting NOCOUNT ON causes issues when editing or deleting data (source).

Community
  • 1
  • 1
Justin
  • 84,773
  • 49
  • 224
  • 367
1

That is not correct, script out the proc an make sure it is not OFF instead o ON, if it is ON it should not return (1 row(s) affected) messages

Also how are you executing the proc

is is just this

exec spTitle_GetTitleById 1
SQLMenace
  • 132,095
  • 25
  • 206
  • 225