I have Procedure and some parametars
Exec ProcedureName @Item = 1
Exec ProcedureName @Item = 2
Exec ProcedureName @Item = 3
Exec ProcedureName @Item = 4
Exec ProcedureName @Item = 5
..etc
this parametar is more value and unkown count
declare @id int;
declare @o int=1;
declare @c int=(select count(*) from item);
while (@o<=@c)
begin
set @id=(select itemid
from item
order by itemid
offset @o rows
fetch next 1 rows only
)
;
print 'exec pr '+cast(@id as varchar(10));
/* exec pr @id;*/
set @o+=1;
end;
The "cursor" /While Loop isn't the problem here and will cause no comparative performance problem. The fact that the stored procedure was written in a RBAR fashion (possibly the correct way to write it. sp_send_dbmail is one such example), is the problem. The only other way to do this is to concatenate a string of commands and that has its own problems and brings nothing to the table for performance.