How to get COUNT from stored procedure return?

Question

I have a stored procedure in SQL which I can't change. It needs few input parameters and returns a table with 100+ rows and several columns.

exec dbo.Select_Data 0, 0, 18, 50

I need something to get count of returned rows:

select count(*) from (exec dbo.Select_Data 0, 0, 18, 50)

and a way to get values from e.g. Name column:

select Id, Name from (exec dbo.Select_Data 0, 0, 18, 50) where Id=10

How do I do this?

The answer probably depends on the exact database you use, so what is it? — Hans Keﬆing, Sep 17 '10 at 08:28

score 6 · Accepted Answer · answered Sep 17 '10 at 08:27

6

You need to create a temp table to hold the results of the stored procedure. you can then query the temp table. The schema of the temp table must match the output of the stored procedure.

Example:

CREATE TABLE #temp
(
ID INT,
NAME VARCHAR(100),
...
)

INSERT INTO #temp
Exec dbo.MyStoredProc

SELECT COUNT(*) FROM #temp

SELECT ID, NAME FROM #temp 
WHERE ID = 10

DROP TABLE #temp

answered Sep 17 '10 at 08:27

codingbadger

42,678
13
95
110

I have a question regarding schema of output table. Do I need to write down the list all the parameters that stored procedure returns? Is there a way to avoid this step? What happens if someone updates that stored procedure without my knowing? – Kromster Sep 17 '10 at 08:32
2

Checking `@@rowcount` instead of `SELECT COUNT(*) FROM #temp` should save a scan of the temp table. – Martin Smith Sep 17 '10 at 08:33
@Krom - The schema of the temp table must match the output from the Stored Procedure not the parameter list. – codingbadger Sep 17 '10 at 08:44
So you mean that it must match exactly and there's no way around it to query just the fields I need (Id, Name)? Cos I suspect that number of returned columns may depend on input parameters.. – Kromster Sep 17 '10 at 08:45
1

You can do but you need to consider some of the additional requirements: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure – Bronumski Sep 17 '10 at 08:48
@Bronumski - that is the exact question I was trying to find. Thanks – codingbadger Sep 17 '10 at 08:51

score 2 · Answer 2 · answered Sep 17 '10 at 08:33

You can insert the data into an in memory or temporary table (depending on the amount of data).

DECLARE @TempTable TABLE
(
     ID INT,
     DATA VARCHAR(20)
)
INSERT INTO @TempTable 
EXEC sp_executesql N'select 1 AS ID, ''Data'' AS DATA'

SELECT
*
FROM @TempTable

How to get COUNT from stored procedure return?

2 Answers2