using SP as a table in a select statement

Posted on 2002-05-20
Last Modified: 2006-11-17
How can i use stored procedure in a select statement as a table
Question by:amnav

Expert Comment

ID: 7021126
To the best of my knowledge this is not possible.  I really wish it was as there are a number of occasions where I'd use it myself.

I think that the only thing you can do it put the results of the SP into a temporary table and use that in your SELECT query.

INSERT INTO #myTable exec sp_GetMyResults

LVL 25

Accepted Solution

Mr_Peerapol earned 48 total points
ID: 7021247
If you are using MS SQL Server 2000, try converting your stored procedure to user-defined function. Then you will be able to use it in a select statement.

Ex. select * from fn_YourFunction(0, 'Hello')

Or else you need to do like the comment above from ColinSnelling.

Hope it helps,
LVL 35

Assisted Solution

by:David Todd
David Todd earned 48 total points
ID: 7028788

The other suggestion is to use the stored procedure to return the record set by passing parameters to it.


Expert Comment

ID: 9280477
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 37
SQL: launch actions one before the other 10 22
SQL Update trigger 5 16
MS SQL + Insert Into Table - If Doesnt Exist 9 32
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question