Stored Procedure to delete records in a table from values in another table-CodePudding

I am having table, table A and table B,C,D etc. table A has three columns namely id,date and tracker.

From table A I have to pick rows which has data more than 30 days from date column which is of datatype date. From the obtained result I have to DELETE records in table B,C,D etc based on the id and tracker(tracker column's data will provide the target tables name) from table A. For example id = 3 from table A will remove the records of id = 3 from table B,C,D etc provided by tracker column.

ID will be the same in both the tables. Table B,C,D etc will have many other columns to it.

CodePudding user response：

So, beeing very simplistic here but

DELETE FROM [Table B]
WHERE
    [Table B].[id] IN (SELECT [Table A].[id] AS [innerID]
                       WHERE [Table A].[date] < DATEADD(DAY, -30, GETDATE()));

It would basically delete all records from Table B where those records have identical id values on Table A and the date column is older than 30 days from today

For the other tables just repeat the process; you can also create a temporary table variable to store that select and use it in the next queries, like this:

DECLARE @ids TABLE(id INT);
INSERT INTO @ids(id)
  SELECT [Table A].[id] AS [innerID]
  WHERE [Table A].[date] < DATEADD(DAY, -30, GETDATE());
DELETE FROM [Table B] WHERE [Table B].[id] IN (SELECT id FROM @ids);
DELETE FROM [Table C] WHERE [Table C].[id] IN (SELECT id FROM @ids);
DELETE FROM [Table D] WHERE [Table D].[id] IN (SELECT id FROM @ids);

From your edit, "tracker column's data will provide the target tables name" you'll have to do something like this (just hinting it, haven't checked all the syntax):

DECLARE @query NVARCHAR(MAX)
SET @query = '' --put the entire query here (all the declarations, and run)
EXEC(@query)

That's very dangerous to do