How to select from stored procedure output

Question

I want to be able to do this:

select rows, reserved from sp_spaceused dummytable

but I can't because "sp_spaceused dummytable" does not return a table, even though it outputs one as a side effect.

What is the workaround?

Answer 1

Try this:

DECLARE @DataSource TABLE
(
    [name] SYSNAME
   ,[rows] CHAR(20)
   ,[reserved] VARCHAR(18)
   ,[data] VARCHAR(18)
   ,[index_size] VARCHAR(18)
   ,[unused] VARCHAR(18)
);

INSERT INTO @DataSource
EXEC sp_spaceused 'dbo.table_name';

SELECT [rows]
      ,[reserved] 
FROM @DataSource;

You can check the official documentation because the system stored procedure can return different number of columns depending on the input parameters. Your table definition must match the returned columns number and type.

Answer 2

You could store the result of the stored procedure into a temporary table (or a table variable, or even a physical table) and then query that one:

--drop table if exists #temp
create table #temp(name nvarchar(100), rows int, reserved nvarchar(100), data nvarchar(100), index_size nvarchar(100), unused nvarchar(100))

insert into #temp
exec sp_spaceused 'dummytable'

select [rows], [reserved] 
from #temp

Answer 3

I am not sure if you want to get the output of the SP in your table. If yes, then you have to create a table structure similar to the output of the SP and then use that table to retrieve the result:

CREATE TABLE #tempData
(
        [Status] VarChar(20),
        SubCategoryId INT,
        ClientName VarChar(200),
        ProjectName VarChar(200),
        EmployeeName VarChar(100),
        Department VarChar(20),
        ManagerName VarChar(100),
        [Role] VarChar(50)
)

Insert Into #tempData
            EXEC [Your SP Name] [Parameters]