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:"
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.