Post reply

Retrieving detaset from one stored procedure into another

  • June 11, 2009 at 2:17 pm

    #72753

    I have a third-party stored procedure that I may not modify. It returns a masked primary key for the next record I to insert into a table. Unfortunately, it returns the value by a SELECT statement rather than as an OUTPUT parameter or RETURN value.

    Is there any way to execute that stored procedure and get the value returned as a dataset without a second round trip? I would like to make the first stored procedure be treated as a view.

    ..... SELECT @NewKey FROM (EXEC SomeProc @input=15) ......

    Many Thanks.

  • June 11, 2009 at 2:25 pm

    #1008253

    You could create a temp table to hold the variable and then use the value from the temp table.

    Something like...

    DECLARE @Tbl TABLE (NewKey varchar(100))

    INSERT INTO @Tbl

    EXEC SomeProc

    SELECT NewKey FROM @Tbl

    There is also a trick using OPENROWSET as in the following example.

    SELECT * FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',

    'exec msdb.dbo.sp_help_job')

  • June 11, 2009 at 2:32 pm

    #1008258

    Thank you, that worked perfectly.

    I had tried the same techique putting the exec in a FROM clause, which failed.

    🙂

  • June 11, 2009 at 2:43 pm

    #1008263

    Great trick that one with OPENROWSET. Very useful. I used to crete a temp table an then populated it with the result of a the system sp, but with this one you can work direct with the result set.

    Thanx!

    Alberto De Rossi
    Microsoft Certified Solutions Associate - SQL Server
    Microsoft MVP - Data Platform
    Power BI User Group Lima - Community Leader

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

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