Save results of stored proc with multiple result sets

Question

Save results of stored proc with multiple result sets

Kathi Kellenberger

SSChampion

Points: 11811
More actions
May 23, 2005 at 12:32 pm

#69086

How do you save the results of a stored proc into temp table (or tables) when you get back multiple result sets? I need to save the results of sp_spaceused in a temp table within a stored proc.

Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Do you have permissions to create tables?

You could either create them in advance and TRUNCATE them at the head of the stored procedure, or create them within the stored procedure and DROP TABLE when you do your clean up.

I wasn't born stupid - I had to study.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

The result set should only have one format, depending on the object type you pass into sp_spaceused. This works for a utility I used in a previous life. The #tdb table holds database info and the #ttbl table holds table info.

create table #tdb (dbname nvarchar(255) NULL,

dbsize nvarchar(255) NULL,

dnunalloc nvarchar(255) NULL,

dbres nvarchar(255) NULL,

dbdata nvarchar(255) NULL,

dbindsize nvarchar(255) NULL,

dbunused nvarchar(255) NULL)

go

insert #tdb exec sp_spaceused

go

select * from #tdb

go

drop table #tdb

go

create table #ttbl (tblname nvarchar(255) NULL,

tblrows nvarchar(255) NULL,

tblreserved nvarchar(255) NULL,

tbldata nvarchar(255) NULL,

tblindsize nvarchar(255) NULL,

tblunused nvarchar(255) NULL)

go

insert #ttbl exec sp_spaceused 'tablename'

go

select * from #ttbl

go

drop table #ttbl

go

And then again, I might be wrong ...
David Webb

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

What I am actually interested in is how, if it is possible, to save multiple result sets to a temp table or tables. If I run sp_spaceUsed without a parameter I get back two tables:

database_name database_size unallocated space -------------- ------------------ ------------------ master 20.69 MB 0.99 MB
reserved data index_size unused ------------ ------------------ ------------------ ------------------ 16328 KB 11440 KB 1632 KB 3256 KB
Can these results be saved in a temp table?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

BOL has return codes of 0 or 1. I was messing with this and could not get it to work.

CREATE TABLE #SpaceUsed( database_name varchar(200),

database_size varchar(10),

[unallocated space] varchar(10),

reserved varchar(10),

data varchar(20),

index_size varchar(10),

unused varchar(10))

INSERT INTO #SpaceUsed EXEC sp_spaceUsed

SELECT * FROM #SpaceUsed

DROP TABLE #SpaceUsed

ERROR MESSAGE....

-- Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 148

-- Insert Error: Column name or number of supplied values does not match table definition.

I will keep playing. Good luck.

I wasn't born stupid - I had to study.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

Hmmmm..

The code I sent worked on my system. sp_spaceused with no arguments only returns one result set for me.

And then again, I might be wrong ...
David Webb