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
('00987172'),
('01013218'),
('01027886'),
('01029552'),
('01031476'),
('01032882'),
('01033085'),
('01034446'),
('01039261')
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
BEGIN
DECLARE @pos        int,
   @nextpos    int,
   @valuelen   int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
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
END
RETURN
END

Then use that function in the SP

CREATE PROCEDURE usp_delete
@RequestIDList varchar(50)
AS
Begin
 DELETE FROM Request as req inner join
 inputParser (@RequestIDList) i on req.Request_ID = i.number
End

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

more