July 25, 2008 at 9:56 am
I have a stored procedure I created that contains a SELECT statement, so when I execute the procedure in the query analyzer, it appears to return a result set, but I would like to join the result set with another table.
I have found many solutions online but they seem to require me to use cursors or openrecordsets (which is a little overkill for my case). I read about using a user defined function, but have not heard any specifics.
I want to do something akin to this, which I haven't heard outright is impossible (other than it doesn't work)
select * from (exec usp_MyProc) as t1
edit: Is this poor practice? Is there some other way I should be doing this?
The procedure I made uses a temporary table then runs a select statement against it to return some of the data, if that helps
---
Dlongnecker
July 25, 2008 at 9:59 am
You cannot do a Select From storedprocedure. You can convert the stored procedure to a table valued function and then you can join on the table valued function. Or you can call the existing stored procedure from another stored procedure and put the results in a temp table:
Insert Into #temp
Exec storedprocedure
Select
From
#temp Join
otherTable on
criteria
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 25, 2008 at 11:06 am
I think Jack has the only good way to do this in 2000.
July 25, 2008 at 11:10 am
Cool! Thanks guys.
Unfortunately UDFs don't allow for temporary tables, but i'm going to be looking into use table varaibles in a bit.
---
Dlongnecker
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply