SQLTeam.com | Weblogs | Forums

How to Exec Procedure with Multi Parametars


#1

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

i need query unused Carsor


#2

To transmit multiple parameters, just add them seperated by comma, like so:
exec procedurename @pn='7632608500-005',@sn='13587682 '

Please explain the "unknown count" and how it should work.

Also you should avoid using cursors.


#3

thank you bitsmed
the procedure need one parameter
example
Create Table Item (ItemID int )
insert into Item
Value (1),(2),(9),(48),(92)

Create procedure Pr
@ItemID int
AS
..........................

I need Execute this Procedure the parameter from Table item
exec pr 1
exec pr 2
exec pr 9
exec pr 48
exec pr 92

and i don't now count row in table item


#4

Try this:

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;

#5

thank you bitsmed
but can we use other queris away from carsor


#6

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.