Dropping tables using dynamic SQL

Question

I am having trouble with my SQL stored procedure, specifically passing in VARCHAR() as a table name using it.

My code (not working) is:

CREATE PROCEDURE DeleteUser 

 @Username VARCHAR(50)

    AS
    BEGIN

    --DROP THE SURF TABLE
    IF EXISTS (SELECT 1 
      FROM sysobjects 
      WHERE xtype='u' AND name=@Username + '_table') 
          DROP TABLE @Username + '_table'  

 END
GO

However, on execution it errors at the DROP TABLE @Username + '_table' line.

What could I be doing incorrectly?

I am using MS SQL Server 2008 if it matters, called from C#.

Answer 1

The DROP TABLE statement can't be parametrised as you are trying. You would need dynamic SQL.

DECLARE @DynSql nvarchar(max) = 'DROP TABLE ' + QUOTENAME(@Username + '_table');
EXEC(@DynSql);