sqlsql-serverdynamic-sqlmysql-variables

Declare a variable based on name in sys.tables then delete the table based on that variable in dynamic SQL


So what I expect is for the first piece of code to find the table name then if that table name exists and is more than 3 days old drop that table.

My issue with this code is that the code is not replacing @temp_name with the actual table DrinkSales. So the variable is not being correctly set in the select statement.

Current Code:

declare @table varchar(100)  = 'DrinkSales' 
DECLARE @temp_name VARCHAR(100)
declare @drop varchar(max) = '

DECLARE @temp_name VARCHAR(100)

select @temp_name= name 
FROM sys.objects
WHERE   DATEDIFF(day, create_date, getdate()) > 3
and name = '''+@table+'''

select @temp_name
                     --if object_id(''dbo.'+@table+''', ''U'') is not null -- needs to be changed to detect if variable is null rather than table.
                     --drop table dbo.'+@table+'

                     '

print(@drop) 
exec(@drop)

So the result should be:

DECLARE @temp_name VARCHAR(100)

select @temp_name= name 
FROM sys.objects
WHERE   DATEDIFF(day, create_date, getdate()) > 3
and name = 'DrinkSales'

select @temp_name
                     --if object_id('dbo.DrinkSales', 'U') is not null -- this should be changed to  
                     --drop table dbo.DrinkSales
                      *if @temp_name is not null *
                        *drop dbo.drinksales*



                     

(1 row affected)

Solution

  • I think you were over-quoting - a common problem in dynamic SQL.

    You can (and should) minimise the dynamic SQL required as follows:

    declare @schema varchar(100) = 'dbo', @table varchar(100)  = 'Proposal', @temp_name varchar(100);
    
    if exists (
      select 1
      from sys.objects
      where datediff(day, create_date, getdate()) > 3
      and [name] = @table
      and [schema_id] = schema_id(@schema)
    )
    begin
      declare @drop varchar(max) = 'drop table ' + quotename(@schema) + '.' + quotename(@table) + ';';
      print(@drop) 
      --exec(@drop)
    end;
    

    Its important to use quotename to protect against SQL injection.

    Note also the addition of the schema as suggested by @David Browne.