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:”

USE abc
GO 

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE OR ALTER PROCEDURE ABC_XYZ_LOAD 
AS 
BEGIN  
    SELECT * FROM [CNT_01] 
END 
GO

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:

SELECT columns FROM (EXEC ABC_ZYZ_LOAD);

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:

DECLARE @Locks TABLE
(
    [spid] int NULL,
    [dbid] int NULL,
    [ObjId] int NULL,
    [IndId] int NULL,
    [Type] sysname NULL,
    [Resource] sysname NULL,
    [Mode] nvarchar(max) NULL,
    [Status] nvarchar(max) NULL
);

INSERT @Locks 
(
  [spid], [dbid], [ObjId], [IndId],
  [Type], [Resource], [Mode], [Status]
)
EXEC sp_lock;

SELECT * FROM @Locks;

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.

2020-01-05