Executing another stored proc inside of a stored proc

  • tacy.highland

    Hall of Fame

    Points: 3270

    Hello,

    Wondering if anyone has any insight into how I can do this:  I have a lengthy stored proc which produces a number of metrics that I may need to use for other calculations.  This stored proc uses OpenQuery to gather the data.  I would like to create another stored procedure that calls this stored procedure and inserts the result set into a table for me to use/manipulate in this new stored proc.

    When I try to create a table variable and then INSERT INTO this table variable with EXEC Storedprocname, I get an error of "An INSERT EXEC statement cannot be nested."  From what I gather, it's due to the OpenQuery that I'm prevented from doing what I need here.

    Are there other ways I can get this stored proc's data so I can do stuff with it in another stored proc?  I'd much rather not have multiple copies of this lengthy stored proc to manage if I can help it, which is why I just wanted to leverage the existing stored proc data.

    Any ideas?

    Thanks!

  • frederico_fonseca

    SSChampion

    Points: 14776

    on the calling proc instead of doing an insert into define a temp table manually.

    on the called proc detect and see if the temp table created on the outer proc exists insert into that table instead of returning a recordset.

     

    that should get around the issue

  • tacy.highland

    Hall of Fame

    Points: 3270

    Not sure I understand.

    To be clear, the proc I'm calling is being used in other reports so I can't necessarily modify it to insert the resultset into a table inside that proc  (or I may have misunderstood your answer, if so let me know).

    Thanks!

  • ScottPletcher

    SSC Guru

    Points: 98620

    You should be able to in this case because whether the called proc inserts into a table or returns the result set directly will be controlled by a new parameter in the proc.  Default the parameter so that existing calls to the proc work exactly as they do today.  Easiest is probably to make the parameter the name of the table to insert the result to, if specified.  If NULL, return a data set as before.

    I'd have to see the proc's code to be any more specific than that.

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • frederico_fonseca

    SSChampion

    Points: 14776

    you would keep the same functionality.

     

    pseudo code - without seeing the proc this may be incorrect but should give you an idea

    original code

    OldProc

    insert into original table from openquery...

    select * from original table

    new codecode

    calling proc

    create table #new_table

    call old proc

    old proc

    insert into original_table from openquery...

    if object_id('tempdb..#new_table') is not null

    begin

    insert into #new_table

    select * from original table

    end

    else

    begin

    select * from original table

    end

     

  • tacy.highland

    Hall of Fame

    Points: 3270

    I'm sorry guys, I'm just not seeing it for whatever reason.

    So proc 1, let's call it dbo.Service_Data.  Uses OpenQuery to get a bunch of data and eventually, ends in a resultset which is just a select columns from #finaltemptable.  (I think I should be doing something here, inserting this into a table that's a parameter?)

    Proc 2, lets call it dbo.Service_Data_Summary, would check if the table above exists and if it does then it inserts into a new table here?

    Do I have that right? (Can you hear how unsure I am here? ; ) )

    How can I check if a #temp table exists between one stored proc to another? I thought temp tables only exist within the execution of a stored proc and are not globally available?

     

  • frederico_fonseca

    SSChampion

    Points: 14776

    temp tables can be defined on proc A and referenced by any proc called by it

    proc A calls proc B

    proc B can then use the temp tables defined by A before the call.

    so your order of operation, because you wish to allow the old proc to work as is if not called by your new proc is

    Proc A

    define temp table A1

    Call proc B (original proc)

    Proc B code

    --- do openquery into its original table

    --- if table A1 exists insert into table A1

    --- else

    --- return recordset

    --- end

    proc A (continuation)

    use data from table A1 as desired

     

     

  • tacy.highland

    Hall of Fame

    Points: 3270

    So how do I actually do this?  Here's my example sprocs:

    ----THIS IS AN EXAMPLE OF PROC 1:
    CREATE PROC dbo.Service_Data
    AS


    DECLARE @SQL NVARCHAR(MAX);

    BEGIN
    DECLARE @DATA TABLE
    (COLUMN1 INT
    ,COLUMN2 INT
    )

    SET @SQL =
    'SELECT
    Column1
    ,Column2
    FROM OPENQUERY(LinkedServer, "SELECT Column1
    ,Column2
    FROM Table
    '')';
    INSERT INTO @DATA
    EXEC sp_executesql @SQL
    END

    SELECT *
    INTO #Sometemptable
    FROM @DATA

    --...do a bunch of queries, etc...



    ----do I add sometihng like this?
    IF Object_ID('tempdb..@MoreData') IS NOT NULL
    BEGIN
    INSERT INTO @MoreData
    SELECT *
    FROM #FinalSummarytable
    END
    ELSE
    BEGIN
    SELECT *
    FROM #FinalSummarytable
    END

    DROP TABLE #Sometemptable,#FinalSummarytable

    Here's an example of proc 2:

    ----THIS IS AN EXAMPLE OF PROC 2:
    CREATE PROC dbo.Service_Data_Summary
    AS

    DECLARE @MoreData TABLE
    (COLUMN1 INT
    ,COLUMN2 INT
    ,COLUMN3 INT
    ,COLUMN4 INT
    )

    INSERT INTO @MoreData
    EXEC dbo.Service_Data

    SELECT *
    FROM @MoreData

    ----do more stuff with this data...etc

    I did a table variable instead of temp table (Does that matter?), and is what I listed above right?

  • frederico_fonseca

    SSChampion

    Points: 14776

    not quite - it needs to be a temp table, not a table variable.

    ----THIS IS AN EXAMPLE OF PROC 2:
    CREATE PROC dbo.Service_Data_Summary
    AS

    create table #MoreData
    (COLUMN1 INT
    ,COLUMN2 INT
    ,COLUMN3 INT
    ,COLUMN4 INT
    )

    -- the exec will insert into the table itself so you do not do the insert ... exec, just the exec
    EXEC dbo.Service_Data

    SELECT *
    FROM @MoreData

    ----do more stuff with this data...etc

     

    On proc 2 you do add the check and insert into the temp table if it exists.

    	----do I add sometihng like this?
    IF Object_ID('tempdb..#MoreData') IS NOT NULL
    BEGIN
    INSERT INTO #MoreData
    SELECT *
    FROM #FinalSummarytable
    END
    ELSE
    BEGIN
    SELECT *
    FROM #FinalSummarytable
    END
  • tacy.highland

    Hall of Fame

    Points: 3270

    I just got a chance to try this out and it ...kinda worked.  Proc B calling Proc A returned the dataset from Proc A with the "EXEC ProcA", but I have to get this data into a temp table so I can further manipulate/summarize it.  How can I do that?  I had the "Select * from #MoreData" listed in the Proc B after the EXEC statement, but it just returned an empty table as the second resultset, (after the resultset it returned from the Proc A above).  Not sure how to get the EXEC resultset into an actual temp table now.

    On a side note, it also did take quite a long time to finish executing, the proc B calling proc A took 1:10 to complete when just executing Proc A by itself takes only 4 sec.  Not sure if there's a performance hit doing it this way or what.

     

  • frederico_fonseca

    SSChampion

    Points: 14776

    I think you misunderstood what I explained or you didn't implement it correctly.

    doing it the way I mentioned the temp table created on proc B will have the contents inserted by proc A and can then be further manipulated by proc B without the need to create any more temp tables.

    see example below

    if object_id('proc_B') is not null 
    drop procedure proc_B
    go

    create procedure proc_B
    as
    begin

    set nocount on;
    set xact_abort on;
    /*
    this proc is a generic use proc that can be executed in two ways

    1 - calling the proc directly - when this is done it will return a record set directly to the caller

    2 - calling the proc from another proc with a temp table #outertable defined prior to the call - when this is done the proc will insert into that table instead of returning a recordset


    */


    if object_id('tempdb..#innertable') is not null
    drop table #innertable

    create table #innertable
    ( schemaname varchar(128)
    , objectname varchar(128)
    )

    insert into #innertable
    select top 10 sc1.name
    , ob1.name
    from sys.objects ob1
    inner join sys.schemas sc1
    on sc1.schema_id = ob1.schema_id

    if object_id('tempdb..#outertable') is not null
    begin
    insert into #outertable select * from #innertable
    end
    else
    begin
    select 'Proc B output', *
    from #innertable
    end

    end
    go


    if object_id('proc_A') is not null
    drop procedure proc_A
    go

    create procedure proc_A
    as
    begin

    set nocount on;
    set xact_abort on;

    if object_id('tempdb..#outertable') is not null
    drop table #outertable

    create table #outertable
    ( schemaname varchar(128)
    , objectname varchar(128)
    )

    exec proc_b
    /*
    at this point table #outertable will contain the records created by proc_b
    we can now manipulate them at will

    */

    select 'Proc A output', *
    from #outertable
    end
    go


    exec proc_b
    exec proc_a
  • tacy.highland

    Hall of Fame

    Points: 3270

    I'm still having a hard time following.  Not sure why it's so challenging.

    I'm going to try to replicate this with a couple of test sprocs and see if I can get it.

    Thank you for your time on this, it's much appreciated.

  • tacy.highland

    Hall of Fame

    Points: 3270

    Ok, frederico, thank you thank you thank you.

    I got a chance to work through it a little bit more, creating the sprocs from your code, then updating them to reference the real data, and it worked.  Apologies it took so long for me to get it, but this is great.  And I learned something new at the same time.

    Thank you again for your expertise and generosity!

Viewing 13 posts - 1 through 13 (of 13 total)

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