Delete from variable name

Question

I want to create a stored procedure in which I have a sourcetable input parameter and I want to use delete statement to delete rows from a destination table corresponding to that source table value, e.g.:

Create proc spdeleteRows
    @sourcetable varchar(50)
As
Begin
    Declare @destname varchar(50)
    Select @destname = destname from tblConfig where sourcetable=@sourcetable
    Delete from @destname where convert(date,loaddate) =getdate();
End

How do I do it?
It shows error at @destname since it is declared as varchar but I cannot declare it as table variable since I will also have to define its structure as well which will be dynamic.
Please help.

Possible duplicate of [Table name as variable](https://stackoverflow.com/questions/2838490/table-name-as-variable) — GSerg, May 15 '20 at 11:30

score 1 · Answer 1 · answered May 15 '20 at 11:35

You would have to use dynamic SQL. I've made a slight assumption on the WHERE, but this is likely what you're after:

CREATE PROC dbo.spdeleteRows @SourceTable sysname AS --corrected spelling, changed to correct data type.
BEGIN

    DECLARE @DestName sysname = (SELECT destname FROM dbo.tblConfig WHERE sourcetable = @SourceTable);

    --changed WHERE, no column converted to a date will ever equal GETDATE(), as datetime has a high precedence than date.
    DECLARE @SQL nvarchar(MAX) = N'DELETE FROM dbo.' + QUOTENAME(@DestName) + N' WHERE loaddate >= CONVERT(date,GETDATE()) AND loaddate < DATEADD(DAY, 1, CONVERT(date,GETDATE()));'; 

    --PRINT @SQL;
    EXEC sys.sp_executesql @SQL;

END;

score 0 · Answer 2 · answered May 15 '20 at 11:33

I think you best bet would be dynamic SQL, that way you would be able to build the query to execute as a string. This isn't exactly recommended though as it's easy to make mistakes when you don't have SSMS supporting you, not to mention the stored procedure can fail if anything changes.

Definitely start by checking sys.tables to see if the name actually exists.

Delete from variable name

2 Answers2