How to use the result of a stored procedure in a MSSQL query

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.

Comments are closed.