Exec SP many times, from select?

Hi All...

if i had the following sp...

*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************

and then i wanted to exec this sp in another procedure i would have

exec my_insert( 1_value, 2_value, 3_value )

My question is how could i exec this will the result set of a select... something like this

exec my_insert (select 1_value, 2_value, 3_value from another_table).

I know i could have this in an insert result type statement ie...

insert into dest_table (select 1_value, 2_value, 3_value from another_table)

but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement

Many Thanks
Gary Tyou can get the values into variables and send the variables into the sp.

select @.var1=select1_Value,@.var2=select2_Value,@.var3=select3_Value from YourTable
exec my_insert(@.var1,@.var2,@.var2)

hth|||Thanks for your help....but

This statement would only call the stored procedure once with the params set to the last selected values.

I would like to call the sp as many times as there are records in the select statement, containing the values for each record.

Cheers
Gary T|||Hi All,

If it's any help to describe the problem, I current carry out this by using a cursor, but I thought there must be an easier (better) way of doing this.

*****************************************************
DECLARE my_cursor CURSOR FOR
SELECT 1_value, 2_value, 3_value from other_table where some_condition

OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec my_insert @.var_1, @.var_2, @.var_3
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
END
CLOSE my_cursor
DEALLOCATE my_cursor
*****************************************************

Cheers
Gary T|||cursors are usually a performance hit, but unless you are processing thousands of rows, it shud be ok. make sure you declare your cursor as :

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT
OPEN rs
fetch next from rs into
WHILE ( @.@.FETCH_STATUS = 0 )
begin
FETCH NEXT FROM rs INTO
END
close rs
deallocate rs

the local,forward_only etc are optional parameters but will make it run faster. check out BOL for more info abt cursors and other parameters.

hthsql