February 5, 2016 at 10:53 am
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?
February 5, 2016 at 11:39 am
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.
February 5, 2016 at 10:09 pm
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.
February 6, 2016 at 2:12 am
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.
Or better, pass it as a table, or do not pass it at all and adapt the stored procedure to read from the source table.
Some huge articles on this topic with lots if ideas, performance tests, etc are all found here: http://www.sommarskog.se/arrays-in-sql.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply