This problem is one I faced several years ago, but today I remembered it and thought someone might need the solution. The problem was simple, I needed to use the result of a stored procedure in a query. The client had used a backend using MS SQL as data store, but couldn’t adjust the database schema.
When I needed to retrieve data from that database, some of the data was calculated in a stored procedure, which I needed to join on data in tables. Of course it was an option to do that in the software I was writing, but I didn’t like that idea, since it could much data and at that time passing tables as a parameter wasn’t possible.
The solution I found isn’t nice and I wouldn’t recommend to use it, unless there is absolutely no other way of achieving the desired result.
Suppose we have a stored procedure that returns a rows with 2 fields, a varchar and an int, and we need to join it on the int field, you could do this:
/*Create a table typed variable,*/ DECLARE @procresults TABLE ( field1 VARCHAR(1000), field2 INT ); /*execute the stored procedure, store the result in the variable*/ INSERT @procresults EXEC storedprocedure /*Use the result in a query*/ SELECT * FROM @procresults pr INNER JOIN otherTable ot ON pr.field2 = ot.id;
I hope you don’t need it, but if you do, I hope it helps you.