Multiple results sets & Temp tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple results sets & Temp tables

Author

Topic

BravehearT
Starting Member

3 Posts

Posted - 2004-04-22 : 06:22:14

Hi there - I'm relatively new to SQL so please be gentle with me.

I am looking at getting a range of details relating to users /databases / roles etc for each of the databases we have.

I am attempting to do this via executing stored procedures and placing the results in a temp table. The idea is to have multiple temp tables and then join them on cetain conditions to provide the final results I require.

My problem is if a stored procedure returns 2 result sets I dont appear to be able to get this information into a temp table!! A single result set returned is not a problem. does anyone know any way of doing this for multiple result sets using Transact SQL.

Thanks in advance
Ronnie
(Scotland)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-22 : 06:34:27

You could either use ## tables or create and drop tables within your procedures...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-22 : 06:39:32

Yes, something like this.

--************************************************
create procedure test
as

create table ##test(Date datetime, Reason varchar(20));

INSERT INTO ##TEST
select * from activity
order by 1

create table ##Test2(CategoryID INT, CategoryName NVARCHAR(15), Description ntext, Picture image)

INSERT INTO ##Test2
select * from categories
order by 1

go

exec test

SELECT * FROM ##TEST;
SELECT * FROM ##TEST2

Duane.

BravehearT
Starting Member

3 Posts

Posted - 2004-04-22 : 06:42:07

Rick - thanks.

Should have mentioned that the Stored Procedures I am running are those supplied by SQL Server themselves (an example of 2 result sets would be shown by executing SP_HELPLOGINS) an I am not writing my own procedures.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-23 : 02:09:48

Have you thought of handling this using ado?
or is that not an option?

Duane.

BravehearT
Starting Member

3 Posts

Posted - 2004-04-23 : 11:19:43

Duane - ADO is not a way I had thought about. I havent used it before but will look into it. Ideally All I'd like to know if it is possible via Transact-SQL Query to add multiple result sets returned from a system stored procedure to a temp table.

Ronnie

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-04-23 : 12:03:18

Since there may be multiple result sets returned you will have to parse the results yourself. Accepting that, you can do this:

create table #tmp (col1 varchar(2000))
insert #tmp exec master..xp_cmdshell 'osql -E -w2000 -Q "exec sp_helplogins"'

-- Parse and process #tmp.col1 as needed
-- All columns from results will be returned on each row

select * from #tmp

This requires xp_cmdhsell permissions allowed for users, trusted connections allowed (see osql -? for other options).

If this request is only to process the results of sp_helplogins, you would be better off making a copy (or 2) of that procedure and modifying it to fit your needs.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources