Convert string to CSV format

Question

Hello,

How can i make the following query to work :-

Declare @Ids as varchar(20);

Set @Ids = '1,2,3,4';

Delete from tblProduct
Where ProductId in (@Ids);

Thanks in advance :)

Answer 1

where ',' + @Ids + ',' like '%,' + cast(ProductId as varchar) + ',%'

But seriously, I would've used a TVF that splits up this string and then you inner join the resulting ids with your table and delete the rows like so:

delete d
from [table] d
join dbo.fnSplitString(@Ids, ',') s on d.id = s.id

Answer 2

You'd have to concatenate the query into a string and then execute that string.

DECLARE @Ids VARCHAR(MAX)
SET @Ids = '1,2,3,4'
DECLARE @QUERY VARCHAR(MAX)

SET @QUERY= 'DELETE FROM tblProduct' + ' WHERE ProductId IN (' + @Ids + ')'

EXEC (@QUERY )

Answer 3

I assume that type of ProductId is integer. You can use a function as follows:

CREATE Function [dbo].[FN_SPLITTER_STRING] (@IDs nvarchar(max), @SEP nvarchar(5))  
Returns @Tbl_IDs Table  (ID nvarchar(500))  As  

Begin 
 -- Append comma
 Set @IDs =  @IDs + @SEP
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @Pos2 Int

 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(@SEP,@IDs,@Pos1)
  Insert @Tbl_IDs Select Substring(@IDs,@Pos2,@Pos1-@Pos2)
  Set @Pos2=@Pos1+LEN(@SEP)
  Set @Pos1 = @Pos1+LEN(@SEP)
 End 
 Return
End

This function takes a @SEP deliminated string (@IDs) and returns a table including the IDs as integers.

Then you can use that function in your stored procedure as follows:

Delete from tblProduct Where ProductId in (select  ID from dbo.FN_SPLITTER_STRING(@Ids,','))