Imagine you have a user interface with a list of products. On the side of each product you have a delete checkbox and then a delete button below the list. Even though a user can delete multiple products at once, you only want to query the database once. One way to achieve this is by sending a comma separated string with product id's to a stored procedure. The stored procedure will then loop through the string, grab each id and delete the corresponding row in the product table.
Here's a stored procedure which does this:
CREATE PROCEDURE DeleteProducts ( @values varchar(8000) ) AS BEGIN SET NOCOUNT ON; declare @value int declare @pos1 int declare @pos2 int -- Append , at the end of string. if substring(@values, len(@values), 1) <> ',' begin set @values = @values + ',' end -- First position. set @pos1 = 1 set @pos2 = charindex(',', @values, 1) while (@pos1 > 0 and @pos2 > 0) begin set @value = cast(substring(@values, @pos1, @pos2 - @pos1) as int) delete from Product where ProductId = @value; -- Next position. set @pos1 = charindex(',', @values, @pos1) + 1 set @pos2 = charindex(',', @values, @pos1) end END
And this is how you would execute it:
exec DeleteProducts '1,20,30,100,250,1000';