0

Can anyone please help with the following case concerning T-SQL: If you have one stored procedure called SP_A which calls inside it's stored procedure called SP_B and you have the following instruction as the first line in SP_A: SET NOCOUNT ON. I know the NOCOUNT variable has a batch scope so it's worth to mention that there are no GO commands anywhere in the body of either stored procedures. Will the NOCOUNT have effect in the child stored procedure (SP_B)?

Example SP_A code:

CREATE PROCEDURE dbo.SP_A
AS
BEGIN
    SET NOCOUNT ON

    EXEC dbo.SP_B
END

Example SP_B code:

CREATE PROCEDURE dbo.SP_B
AS
BEGIN
    SELECT * FROM dbo.SOME_TABLE
END

Will the select from the SP_B procedure print number of affected rows?

vinkomlacic
  • 1,822
  • 1
  • 9
  • 19
  • 1
    Look at DONE_IN_PROC I think it will work in this case and not count rows from SP_B. Look at https://stackoverflow.com/questions/1483732/set-nocount-on-usage for more detailed NOCOUNT info. – Wesley Nightingale Jun 28 '19 at 11:55
  • 1
    Sidenote: I hope your actual procedures do not have names that start with `SP_` Look at [this](http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix) – GuidoG Jun 28 '19 at 12:00
  • @GuidoG this code was just for demonstration purposes though I did not know about this. After reading a little bit about this subject I found that if the stored procedure names are case sensitive (which depends on DB setting) SP_ and sp_ are two different prefixes. Anyway, I will refrain from prefixing procedure names with both prefixes to avoid ambiguity so thanks :). – vinkomlacic Jun 28 '19 at 12:07
  • 1
    Stored procedures do not, themselves, print anything at all. The `x row(s) affected` you can see is what SSMS reports back when SQL Server tells it how many rows were affected. If `SP_A` turns it off and `SP_B` does not turn it back on, then when you execute `SP_A`, you won't get back any counts of rows -- it's really as simple as that. (`GO` or not is not relevant because you can't use this anyway -- T-SQL statements cannot begin new batches themselves, so everything inside one stored procedure call, including any calls to other stored procedures, happen in the same batch.) – Jeroen Mostert Jun 28 '19 at 13:12
  • 2
    As to the question of whether it is *correct* to depend on this behavior, that's a completely different matter. If the intent is to never get row counts, then obviously it would be a bad idea to count on `SP_B` always being called from `SP_A`. It makes no sense to omit `SET NOCOUNT ON` as a sort of optimization. – Jeroen Mostert Jun 28 '19 at 13:14
  • 2
    Regarding what you mentioned about `GO` commands in the proc body, that's not worth mentioning since it impossible to include `GO` commands in a in a stored procedure. The `GO` batch separator is not a T-SQL command and is only recognized by SQL Server tools and some SMO APIs. – Dan Guzman Jun 28 '19 at 13:48
  • Thanks everyone for clarifying – vinkomlacic Jun 28 '19 at 13:58

1 Answers1

3

SET NOCOUNT ON will prevent row count messages from being sent by the stored procedure, or by any nested stored procedures (or dynamic batches). If a nested procedure or batch sets NOCOUNT OFF, row count messages will be sent for the statements in that batch, but the NOCOUNT OFF behavior will be restored when control returns to the outer proc/batch.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67