Is it correct to use SET NOCOUNT ON only in the root stored procedure in case you want to call other stored procedures in your root stored procedure

Question

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?

Look at DONE_IN_PROC I think it will work in this case and not count rows from SP_B. Look at stackoverflow.com/questions/1483732/set-nocount-on-usage for more detailed NOCOUNT info. — Wesley Nightingale
Sidenote: I hope your actual procedures do not have names that start with SP_ Look at this — GuidoG
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
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
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

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2019-06-28T13:13:38

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.

Is it correct to use SET NOCOUNT ON only in the root stored procedure in case you want to call other stored procedures in your root stored procedure

1 Answers