MS SQL: Suppress return value of stored procedure called in stored procedure

Question

I think I have the same problem as kcrumley describes in the question "Problem calling stored procedure from another stored procedure via classic ASP". However his question does not really include an solution, so I'll give it another shot, adding my own observations:

I have two stored procedures:

CREATE PROCEDURE return_1 AS BEGIN
    SET NOCOUNT ON;
    SELECT 1
END

CREATE PROCEDURE call_return_1_and_return_2 AS BEGIN
    SET NOCOUNT ON;
    EXEC return_1
    SELECT 2
END

Note that both procedures contain "SET NOCOUNT ON". When I execute "call_return_1_and_return_2" I still get two record sets. First the value 1, then the value 2.

That throws ASP (classic VBScript ASP) off the tracks.

Any hints on how I can suppress the first result set? Why is it there even with NOCOUNT?

Skipping the first record set in ASP is not an option. I need a "database only" solution.

Answer 1

As Matt points out in his comment, neither solution really 'swallow' the first resultset. I don't know why you'd want this but you can 'swallow' the result of the first exec by using a table variable. It must match the exact amount and type of the result set's columns. Like so:

CREATE PROCEDURE return_1 AS 
    SET NOCOUNT ON;
    SELECT 1
GO
CREATE PROCEDURE call_return_1_and_return_2 AS 
    SET NOCOUNT ON;
    DECLARE @Result TABLE (res int)
    insert into @Result EXEC return_1
    SELECT 2
GO