passing results to another SP - SQL Server Forums

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)

passing results to another SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-21 : 10:20:40

Roger writes "Hey Guys,
Ok I really need the expert's help. I did myu homework, look up books and other resources, but I want to try to seek help here. I am running a query as follows

Create SP_select_Somethin

Select UserID from UsersTbl where <condition>

--I want to pass the results to another nested SP
--I can't see to find out how to do so...

exec SP_EmailUsers

I know this propbaly seems to be a beginner's question, but I want to see how to do it.
Thanks,"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-21 : 19:27:09

This will require a procedural approach to process the records. One option would be to create a loop or cursor and to move through the primary resultset storing the values in local variables and passing them into the secondary email proc. Though this may better be handled in another tier.

gboulton
Starting Member

6 Posts

Posted - 2004-04-21 : 19:59:53

One solution is:

create sp_select_somethin
@condition varchar(50) -- or whatever datatype
as
declare @userid varchar(10) -- or whatever datatype is appropriate
declare userid_rs cursor for select userid from userstbl where column_x=@condition
open userid_rs
fetch next from userid_rs into @userid
while @@fetch_status=0
begin
exec sp_emailusers @userid
--handle whatever output sp_emailusers returns here
fetch next from userid_rs
end
close userid_rs
deallocate userid_rs

Go

exec SP_select_Somethin @condition='blahblah'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources