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

[hubert.trzewik@gmail.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

[Simon Hayes]

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


<hubert.trzewik @gmail.com> wrote in message
news:1110986308 .176019.292290@ l41g2000cwc.goo glegroups.com.. .[color=blue]
> 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
>[/color]

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.TableWithPr ocResults -- or from dbo.MyTableFunc tion()

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@gmail.com]

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

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