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#.

Msg 102, Level 15, State 1, Procedure DeleteUser, Line 21 Incorrect syntax near '@Username'. Line 21 is the DROP TABLE line. — Brett
– Brett, Commented May 10, 2011 at 21:55

Martin Smith · Accepted Answer · 2011-05-10 22:15:04Z

11

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);

edited May 10, 2011 at 22:15

answered May 10, 2011 at 21:53

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dropping tables using dynamic SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related