[SOLVED] MS SQL: Suppress return value of stored procedure called in stored procedure

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

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.

Solution

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'