0

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. – spaghettidba Apr 13 '15 at 10:41
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 Apr 13 '15 at 10:00
  • Stijn Thanks for the idea, Is there any other possibilities without using #temp table or table variable? – lourdh Apr 13 '15 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 '15 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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