How to get COUNT from return of stored procedure?

Question

I have a stored procedure in SQL that I cannot modify. It requires several 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 the number of rows returned:

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

and a method of obtaining values, for example. Column Designation:

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

How to do it?

Answer 1

You need to create a temporary table to store the results of the stored procedure. you can query the temp table. The temp table schema should 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 data into a 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