SQL: Retrieve the results of executing a stored procedure in T-SQL

There was a recent question asked on Stack Overflow. The poster asked:

"I have this stored procedure and I need to fetch result of select query as output:"

Now, it always annoys me when the Stack Overflow rule enforcers decide that there wasn't enough info there and close it. (Particularly when it was a new contributor).

He really was asking a pretty common question about how you execute a stored procedure, and get access to the output of the procedure within the T-SQL code. People hope you can write code like this:

But that isn't valid syntax.

What you need to do

What you need to do instead is to create a table variable (or temp table) to hold the output, and to then use an INSERT … EXEC to get the values. You also want to avoid having an asterisk (*) in the SELECT clause in the procedure. Be explicit about what's being returned.

Here's an example:

The procedure sp_lock returns some locking info. I've shown it executed in the main image above. If we need to use the data that it returns in a T-SQL query, we just do this:

And the output from the last SELECT statement is as expected:

Now I selected it at the end, but of course you don't need to do that. You could then just use the @Locks table variable in the rest of your code.

Note also: I used the column names that the stored procedure normally returns. Can't say I love them. You could have better names in the table variable.




Leave a Reply

Your email address will not be published. Required fields are marked *