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?

Answer 1

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

Answer 2

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