Ed Wagner (2/5/2016)

---

Luis Cazares (2/5/2016)

---

curious_sqldba (2/5/2016)

---

I have a stored procedure which takes varchar type variable as an input, the variable should be able to loop through multiple values and execute for each value in serial way.

Declare @tempvalue nvarchar(max)

-- @tempvalue will loop through many different values

exec mysproc @myvariable

-- how do i loop @myvariable through multiple values?

It depends. How are you going to populate the @tempvalue variable? If it's from a table, use a cursor.

If possible, remove the cursor and let the procedure to work with sets instead of scalar values.

Completely agreed. If the the parameters will fit inside a non-LOB string, you can use a set-based split on the string to transform it into a temp table. From there, your procedure can use the temp table like any other table and do the work you need it to do. Passing individual values to the procedure and calling it repeatedly will likely take a lot longer to run that it needs to.