I was working at home and extending a gridview control with some extra features I required. I have added one column with checkbox for multiple delete. To delete multiple records I need to pass either multiple ID to SP and delete all or can pass each ID one by one to SP.
I passed all ID as coma separated string to SP. I used split function to split the coma separated ID, so that I can use those ID in inner query. For example
DELETE FROM TableName WHERE ID IN (SELECT dbo.fun_Split(@AllID, ',' ))
Here you have to pass string value and separator as a parameter. In my case, I have used "," as seperator. Below is the function,
Create function fun_Split (@String nvarchar(4000), @Delimiter char(1))
Returns @Results Table (Items nvarchar(4000))
Declare @Index int
Declare @Slice nvarchar(4000)
Select @Index = 1
If @String Is NULL
While @Index != 0
Select @Index = CharIndex(@Delimiter, @String)
If (@Index != 0)
Select @Slice = left(@String, @Index - 1)
Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)
If Len(@String) = 0
Fig -(1) Split function SQL.
There is another way to do the same. You can use open XML format to pass more than one parameters to SP. I will explain that in my next blog.