Remove a row in an arbitrary table using data from a select statement

Question

I have a table called "changes" which contains a list of record numbers the user has modified on many other tables. I'd like to remove the record referenced by a row in changes (and the changes row itself) in one SQL query.

WITH name AS ( SELECT tableName FROM [changes] WHERE id = 80 ),
   number AS ( SELECT  changeRecnum FROM [changes] WHERE id = 80 )

DELETE FROM [name] WHERE changeRecnum = number

DELETE FROM [changes] WHERE id = 80

Unfortunately, I keep getting errors about how 'changeRecnum' and 'number' are invalid column names.

Answer 1

You need to use Dynamic SQL to use a variable as a table name. Best way I think would be to set your name and number as @variables.

DECLARE @name varchar(100)
DECLARE @number int
DECLARE @SQL varchar(max)

SET @name = (SELECT tableName FROM [changes] WHERE id = 80)
SET @number = ( SELECT  changeRecnum FROM [changes] WHERE id = 80 )

SET @SQL = 'DELETE FROM [' + @name + '] WHERE changerecnum = ' + CAST(@number as varchar)
PRINT (@SQL)
EXEC (@SQL)

DELETE FROM [changes] WHERE id = 80

You need to read this article about Dynamic SQL though. It's bascially required reading for these scenarios.