Using SELECT output from stored proc in another SELECT FROM?

Question

Let stored proc be like this:

Code Snippet

CREATE PROCEDURE dbo.get_some_data

 ...
AS

SELECT * FROM anywhere ...

GO

 

 

Now, I want to use it like this:

Code Snippet

SELECT * FROM (EXEC get_some_data ...) WHERE some_condition

 

 

meaning that the proc returns SELECT data.

 

But, alas! "Incorrect syntax near the keyword 'exec'." !!!

(SQL Server 2000)

 

Is it possible what I need?

Answer 1

There are several ways to accomplish what you are trying to do.  A key question associated with this is whether or not the intended procedure updates anything.  Please comment on this.  Methods to accomplish what you want include:

• Using INSERT INTO table EXECUTE procedure and then using the table for JOIN purposes -- typically a temp table or a table varable (if using SQL 2005) is used for this
• You might want to consider converting your procedure to a function -- especially an inline function -- if possible.  (This is not possible if the procedure updates anything)
• You might be able to use the OPENROWSET function to encapsulate the procedure.  (This will work even if the procedure does any updates; however, under these circumstances this idea becomes nasty)

 

Answer 2

Kent Waldrop Au08 wrote:

Using INSERT INTO table EXECUTE procedure and then using the table for JOIN purposes -- typically a temp table or a table varable (if using SQL 2005) is used for this You might want to consider converting your procedure to a function -- especially an inline function -- if possible.  (This is not possible if the procedure updates anything) You might be able to use the OPENROWSET function to encapsulate the procedure.  (This will work even if the procedure does any updates; however, under these circumstances this idea becomes nasty)

1. I use this method now, but it do not allow me for use it as subqueries (in other SELECT), i.e. to build temp table "dinamically" inside another SELECT query.
2. I can not use function coz functions can't call stored procs (while I need to call them inside my proc).
3. I will consider OPENROWSET yet.

Answer 3

you can wrap the original select into a function instead of a proc.

Answer 4

Try exec into a GLOBAL temp table.  You should be able to then refer to the table in other procs or queries.

 

--This is your table definition

create table ##test

(

test_id int,

test varchar(6)

)

insert into ##test

(

test_id,

test

) exec sp_myProc

 

--You can run this from anywhere on the server in another session.

select * from ##test

 

drop table ##test

Answer 5

SalJ wrote:

Try exec into a GLOBAL temp table.  You should be able to then refer to the table in other procs or queries.   --This is your table definition create table ##test ( test_id int, test varchar(6) ) insert into ##test ( test_id, test ) exec sp_myProc   --You can run this from anywhere on the server in another session. select * from ##test   drop table ##test

By using a global temp table you introduce a whole new set of problems, including dirty reads and concurrency, if multiple users execute code to create the global temp table.

 

I would highly discourage the use of a global temp table.

Answer 6

True, if you run multiple times by different users, then you may have issues if you don't control properly.

 

Answer 7

syi916 wrote:

you can wrap the original select into a function instead of a proc.

Unfortunately, I can't! Since UDFs can't call stored procs (what I need too). (SQL 2000)

But, can SQL 2005 do it?

Answer 8

UDFs can call stored procs (by first calling an extended proc) :-)

 

Here is a way to accomplish this but I myself would not run this on a production box

http://sqlservercode.blogspot.com/2008/05/how-to-log-when-function-is-called.html

 

Denis The SQL Menace

SQL Server Programming Hacks

ASP.NET hacks

 

 

 

Answer 9

Here is the OPENROWSET example

 

Store The Output Of A Stored Procedure In A Table Without Creating A Table

 

Keep in mind that this is a loop back query

 

Denis The SQL Menace

SQL Server Programming Hacks

ASP.NET hacks