EXEC stored procedure for every line of SELECT result table - how?

5 views
Skip to first unread message

hubert....@gmail.com

unread,
Mar 16, 2005, 10:18:28 AM3/16/05
to
Hello,

Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.

I guess it's possible with cursors, but maybe it's possible to make it
easier.

Give an example, please.

Thank you in advance.

Hubert

Simon Hayes

unread,
Mar 16, 2005, 1:59:10 PM3/16/05
to

<hubert....@gmail.com> wrote in message
news:1110986308.1...@l41g2000cwc.googlegroups.com...

A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:

http://www.sommarskog.se/share_data.html

After that, you can do something like this:

declare @c1 int, @c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()

open cur
fetch next from cur into @c1, @c2...
while @@fetch_status = 0
begin
exec dbo.MyProc @c1, @c2, ...
fetch next from cur into @c1, @c2...
end

close cur
deallocate cur

Simon


hubert....@gmail.com

unread,
Mar 17, 2005, 6:43:36 AM3/17/05
to
Thanks. It was quite fast and easy to create such procedure.

Reply all
Reply to author
Forward
0 new messages