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