Sql Server How to remove multiple tables based on an arbitrary array of values?


I have an arbitrary list of values and I want to delete records across multiple tables using T-SQL. I would like to re-use the script in the future with different lists of values. This is for debugging purposes only (I just want to clear out records so they can be re-imported with the new version of the software), so it doesn't need to be pretty.

So far I have:

DECLARE @RequestIDList table(Request_ID nvarchar(50) NOT NULL)
INSERT INTO @RequestIDList (Request_ID) VALUES
DELETE FROM Request WHERE Request_ID IN (SELECT Request_ID FROM @RequestIDList)
DELETE FROM RequestTest WHERE Request_ID IN (SELECT Request_ID FROM @RequestIDList)

It seems to work, but is there a better way? I can't seem to work out how to use a variable directly with an IN clause (e.g. "WHERE Request_ID IN @RequestIDList").

First you need to create a function which parses the input

CREATE FUNCTION inputParser (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
DECLARE @pos        int,
   @nextpos    int,
   @valuelen   int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
                      THEN @nextpos
                      ELSE len(@list) + 1
                 END - @pos - 1
INSERT @tbl (number)
  VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos

Then use that function in the SP

@RequestIDList varchar(50)
 DELETE FROM Request as req inner join
 inputParser (@RequestIDList) i on req.Request_ID = i.number

EXEC usp_delete '1, 2, 3, 4'

For furthur details please have a look at this article .It explains differnt methods depending on the sql server version .For SQl server 2008 it uses TVP which further simplifies the input parser