How do I paramaterise a T-SQL stored procedure that drops a table?

Question

I'm after a simple stored procedure to drop tables. Here's my first attempt:

CREATE PROC bsp_susf_DeleteTable (@TableName char)
AS
IF EXISTS (SELECT name FROM sysobjects WHERE name = @TableName)
BEGIN
DROP TABLE @TableName
END

When I parse this in MS Query Analyser I get the following error:

Server: Msg 170, Level 15, State 1, Procedure bsp_susf_DeleteTable, Line 6
Line 6: Incorrect syntax near '@TableName'.

Which kind of makes sense because the normal SQL for a single table would be:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tbl_XYZ')
BEGIN
    DROP TABLE tbl_XYZ
END

Note the first instance of tbl_XYZ (in the WHERE clause) has single quotes around it, while the second instance in the DROP statement does not. If I use a variable (@TableName) then I don't get to make this distinction.

So can a stored procedure be created to do this? Or do I have to copy the IF EXISTS ... everywhere?

Answer 1

You'll have to use EXEC to execute that query as a string. In other words, when you pass in the table name, define a varchar and assign the query and tablename, then exec the variable you created.

Edit: HOWEVER, I don't recommend that because someone could pass in sql rather than a TableName and cause all kinds of wonderful problems. See Sql injection for more information.

Your best bet is to create a parameterized query on the client side for this. For example, in C# I would do something like:

// EDIT 2: on second thought, ignore this code; it probably won't work
SqlCommand sc = new SqlCommand();
sc.Connection = someConnection;
sc.CommandType = Command.Text;
sc.CommandText = "drop table @tablename";
sc.Parameters.AddWithValue("@tablename", "the_table_name");
sc.ExecuteNonQuery();

Answer 2

You should be able to use dynamic sql:

declare @sql varchar(max)
if exists (select name from sysobjects where name = @TableName)
BEGIN
   set @sql = 'drop table ' + @TableName
   exec(@sql)
END

Hope this helps.

Update: Yes, you could make @sql smaller, this was just a quick example. Also note other comments about SQL Injection Attacks

Answer 3

Personally I would be very wary of doing this. If you feel you need it for administrative purposes, please make sure the rights to execute this are extremely limited. Further, I would have the proc copy the table name and the date and the user executing it to a logging table. That way at least you will know who dropped the wrong table. You may want other protections as well. For instance you may want to specify certain tables that cannot be dropped ever using this proc.

Further this will not work on all tables in all cases. You cannot drop a table that has a foreign key associated with it.

Under no circumstances would I allow a user or anyone not the database admin to execute this proc. If you havea a system design where users can drop tables, there is most likely something drastically wrong with your design and it should be rethought.

Also, do not use this proc unless you have a really, really good backup schedule in place and experience restoring from backups.