Using SQL parameters to DROP INDEX in C#

Question

I'm trying to follow best practice (and also remove Visual Studio Code Analysis warnings) by using parameters when dropping a SQL Server index.

Not using parameters works fine:

string tableName = "dbo.TableName";

SqlCommand sqlCommand = new SqlCommand("DROP INDEX Blah ON " + tableName);

sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();

However, when I try to use a parameter I get an error

Incorrect syntax near '@TableName'.

Code:

string tableName = "dbo.TableName";

SqlCommand sqlCommand = new SqlCommand("DROP INDEX Blah ON @TableName");

sqlCommand.Parameters.Add(new SqlParameter("TableName", tableName));

sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();

What am I doing wrong?

Answer 1

You are doing nothing wrong. Parameters cannot be used to replace identifiers -- column names/aliases, table names/aliases, schema names, and database names. They also cannot be used to replace function names or operators or keywords.

That is a long list. They can be used to replace constants in the query.

I guess the way to remember this is that the parameterized query can be pre-compiled. In order to compile a query, all object references need to be resolved -- so the values cannot be provided by parameters.

You have already solved the problem by putting the table in the string. You can use quotename() to help protect against injection (see here).

Answer 2

DROP INDEX is a DDL statement, most DDL statements don't accept parameterized values. The best you can do is use dynamically constructed SQL and escape the table name using QUOTENAME

string tableName = "dbo.TableName";

string sql = @"
declare @sql nvarchar(500)
set @sql = N'DROP INDEX Blah ON ' + QUOTENAME(@TableName)
exec sp_executesql @sql
";

SqlCommand sqlCommand = new SqlCommand("");

sqlCommand.Parameters.Add("@TableName", SqlDbType.NVarChar, 50).Value = tableName;

sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();

I also updated your code to use the more "normal" way to add a parameter, explicitly setting the datatype of the parameter.