SQLTeam.com | Weblogs | Forums

Use the stored proc name on select statement


#1

Hello,

How do I use the stored procedure as my FROM in my SELECT statement?

Thanks all


#2

You cannot. You can use functions, but not stored procedures.

The work around is to use the INSERT..EXEC construct, but it has some limitations. Look at the examples on this page.

For example:

--INSERT...EXECUTE procedure example  
INSERT INTO dbo.EmployeeSales   
EXECUTE dbo.uspGetEmployeeSales;  
GO

Then select from that table.


#3

Or, use OPENROWSET.


#4

Great, thanks James and Jeff


#5

My usual answer to that question is "If you want to get there you ought not to start from here" !!

A different option would be to have the SProc store the data into a table (temporary or otherwise) so that "some external process" can then use that data.

If an Sproc returns more than one resultset (now or, more devastatingly "at some future upgrade") the INSERT INTO EXEC will no longer work. If the column names don't match (now ... or in future)... it will break.

Whereas if the SProc stores the data into a Table and then an extra column is added in future then existing code is most likely to still work, including if in future the Sproc is modified to create two temporary tables, representing two resultsets.

If the Sproc is yours (rather than a 3rd party object which you cannot change) I would recommend considering reviewing how you solve this, rather than just assuming that diverting the output is the best/only way forward. Particularly if a VIEW could be created, instead of an SProc :sunglasses:


#6

Thank you Kristen