SQL Server : execute stored procedure from another stored procedure

Question

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.

• spaghettidba: If it only returns data without modifying it, maybe that stored procedure should be a function.

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

• vonPryz: Please keep in mind that table variables lack statistics and thus might throw the query optimizer to very wrong a path.
• lourdh: Stijn Thanks for the idea, Is there any other possibilities without using #temp table or table variable?
• Stijn Wynants: 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/…