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

Its not the NOCOUNT thats causing this, your stored procedures have a select each so each one is coming in its own result set. This could be avoided by changing your first stored procedure to use output parameters to pass the number 1 back rather than doing a select. The second stored procedure could then examine the output parameter to get the data it needs to run.

Try something like this

CREATE PROCEDURE Proc1
(
    @RetVal INT OUTPUT
)
AS
SET NOCOUNT ON
SET @RetVal = 1

CREATE PROCEDURE Proc2
AS
SET NOCOUNT ON
DECLARE @RetVal int
EXEC    [dbo].[Proc1]
     @RetVal = @RetVal OUTPUT
SELECT  @RetVal as N'@RetVal'

Answer 2

Those are not return variables, but output record sets. I guess that as soon as SQL server flushes output to client, you're screwed and can't take it back.

I would solve this by adding a parameter to SP return_1, that would control if return_1 would select records or just do stuff and silently exit.

Answer 3

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

Answer 4

   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
    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
    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
    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
    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

Answer 5

Thank you edosoft, that worked a treat :)