locked
how to capture output of procedure within another procedure RRS feed

  • Question

  •  

    Hello,

     

    I am creating a temporary table and trying to insert one of the column's value through a stored procedure output.

     

    select @variable= (exec procedure @parameter)  -- Can we do something like this?

     

    insert into #temp_table

    SELECT field1, field2, field3 from table

     

    insert into # temp_table ( field4) values(@variable)

     

     

    Please let me know.

     

     

    Thursday, August 7, 2008 6:16 AM

Answers

  • It's not necessary to first put it in a variable:


    Code Snippet

    insert into # temp_table (field4)
    exec procedure @parameter


    Thursday, August 7, 2008 7:03 AM

All replies

  • It's not necessary to first put it in a variable:


    Code Snippet

    insert into # temp_table (field4)
    exec procedure @parameter


    Thursday, August 7, 2008 7:03 AM
  • If you are interested in capturing a single value another option is to modify the stored procedure to return an OUTPUT parameter.  Also, the INSERT INTO ... EXEC procName syntax does not nest.  So if you start to nest stored procedures you will need to be careful to not use the INSERT INTO ... EXEC syntax at different nesting levels.

    • Proposed as answer by Barbi Rio Wednesday, February 25, 2009 11:16 AM
    • Unproposed as answer by Ch. Rajen Singh Monday, September 30, 2013 4:52 AM
    Thursday, August 7, 2008 6:41 PM
  • The code:

    select @variable= (exec procedure @parametercan be exec procedure @parameter, @variable OUTPUT

     

    and you have to change your procedure that you are calling with exec to have an output variable @variable

    • Proposed as answer by Barbi Rio Wednesday, February 25, 2009 11:16 AM
    • Unproposed as answer by Ch. Rajen Singh Monday, September 30, 2013 4:52 AM
    Monday, August 11, 2008 10:56 AM