Wednesday, June 4, 2008

Handling comma separated strings in stored procedures

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';