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

[hubert.trzewik]

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]

<hu************@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

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

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.trzewik]

Thanks. It was quite fast and easy to create such procedure.