how to return a single result set from a stored procedure

  • I have a stored procedure that calls a few other stored procedures which each return a single result set.

    The main stored procedure when run, outputs multiple results, each coming from one of the nested stored procedures being called from within. At the end of the main stored procedure, I have a final big query that I want to return as the only result set.

    How can I do this without rewriting the nested stored procedures to create output parameters(that code isn't under control)? My main stored procedure has SET NOCOUNT ON in the beginning.

    I am using Query Analyzer from SQL 2000 SP4 but connecting to a SQL 7 db.

    Thanks,

    Erik

     

  • You could store the results of the procedures in a table and select from that table..!!!







    **ASCII stupid question, get a stupid ANSI !!!**

  • I tried what you suggested but I still receive the additional result set from the nested stored procedure.

    CREATE PROCEDURE dbo.my_main_sp

    @email Varchar(80)

    AS

    SET NOCOUNT ON

    DECLARE @rc Char(3), @Message Varchar(100), @userid INT

    --this sp returns a result set, not output parameters

    EXEC dbo.my_nested_sp @email = 'test@test.com'

    CREATE TABLE #Temp

    (RC Char(3), Message Varchar(100), userid INT)

    INSERT INTO #Temp

    (RC, Message, pk)

    Values (@RC, @Message,@userid)

    SELECT * FROM #Temp

    DROP Table #Temp

    RETURN

  • Is this what you are looking for:

    create procedure uspNest

    as

     select top 10 uid, status

     from model.dbo.sysusers

    go

    create table #Tempo

     (uid int,

     status int)

    insert into #Tempo  -- This captures what uspNest returns

    exec uspNest

    select uid, status from #Tempo

    drop table #Tempo

     

  • Yes! That works.

    INSERT INTO #temp

    EXEC dbo.my_nested_sp @email = 'test@test.com'

    Thank You!

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply