SQL Server : execute stored procedure from another stored procedure

Question

I have two stored procedures.

Proc1 executes a SQL statement and results a data table as a result set.
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. — spaghettidba, Apr 13, 2015 at 10:41

Stijn Wynants · Accepted Answer · 2015-04-13 09:53:39Z

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

answered Apr 13, 2015 at 9:53

Stijn Wynants

1,74212 silver badges17 bronze badges

1

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

Add a comment |

1 Answer 1