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);
Add a comment
|
3 Answers
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,','))
1 Comment
Denis Valeev
The name of the function should be FN_STRING_SPLITTER
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
Comments
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 )
