Deleting tables using dynamic SQL

Question

I am having problems with my SQL stored procedure, in particular being passed to VARCHAR() as the name of the table using it.

My code (doesn't work):

 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, when executed, these are errors in the DROP TABLE @Username + '_table' .

What can i do wrong?

I use MS SQL Server 2008, if it matters, is called from C #.

Answer 1

The DROP TABLE statement cannot be parameterized as you try. You will need dynamic SQL.

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