How To Get One Dataset From Multiple Dataset SP

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 2008 Forums

Transact-SQL (2008)

How To Get One Dataset From Multiple Dataset SP

Author

Topic

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-11 : 15:20:20

I have a stored proc that returns more than one dataset. I would like to use the results of that sp in another sp. For instance:


DECLARE @return_value int;

EXEC  @return_value = sp_helpdb 'master'
--    @TemplateID = 1002;

SELECT @return_value as N'@Return Value';

This returns two sets of data. I want to use the data found in the first set, in another SP. Is there a way to do this without using CLR or insert-exec? I am running SQL Server 2008R2.

Pseudo-Code Sample:


SELECT name, db_size from EXEC(sp_helpdb 'master')[0]

FischMan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-11 : 15:25:44

There are a few different ways, described here with examples etc: http://www.sommarskog.se/share_data.html

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-11 : 15:55:44

quote:
Originally posted by sunitabeck

There are a few different ways, described here with examples etc: http://www.sommarskog.se/share_data.html

Yeah - I read that. Very informative. So, it appears that what I want to do requires CLR.

FischMan

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-01-11 : 16:44:25

Not at all.

Lookup table value function. You can select the results just like how you illustrated. You would create a function that returned the result questions or whatever in a table, then you could use like so.



Select * from
MYTEMPLATEIDTABLE a
cross apply 
dbo.fn_runtemplatequestionscenerio(a.templateid) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources