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
ENDAnd this is how you would execute it:
exec DeleteProducts '1,20,30,100,250,1000';