I have two stored procedures.

  1. Proc1 executes a SQL statement and results a data table as a result set.
  2. I need to use the datatable in a select SQL that resides in proc2 as a datasource. like,

    SELECT Col1 
    FROM (Exec Proc1)

How can I do it? This syntax here shows error in SQL Server.

  • If it only returns data without modifying it, maybe that stored procedure should be a function. Commented Apr 13, 2015 at 10:41

1 Answer 1


you could store your result in a Table Variable

DECLARE @TableVar TABLE (col1 varchar,col2 int,...)
insert into @TableVar
exec (Proc1)

select Col1 from @TableVar
  • 1
    Please keep in mind that table variables lack statistics and thus might throw the query optimizer to very wrong a path.
    – vonPryz
    Commented Apr 13, 2015 at 10:00
  • Stijn Thanks for the idea, Is there any other possibilities without using #temp table or table variable?
    – lourdh
    Commented Apr 13, 2015 at 11:12
  • 1
    You cannot use the results of a stored proc directly - you need to store that into an in-memory or temporary table, this a duplicate of your question :) stackoverflow.com/questions/2847327/… Commented Apr 13, 2015 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.