CODEWITHSUNDEEP
sql-server-2005
TCM
TCM

Reputation: 16900

Convert string to CSV format

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

Upvotes: 2

Views: 171

Answers (3)

Martin Beeby
Martin Beeby

Reputation: 4599

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 )

Upvotes: 1

Zafer
Zafer

Reputation: 2190

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,','))

Upvotes: 2

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 1

Related Questions