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?

@EricJ. SqlParameter will automatically add the @ if it is missing. — Scott Chamberlain
– Scott Chamberlain, Commented Jun 28, 2016 at 2:18

Gordon Linoff · Accepted Answer · 2016-06-28 02:11:18Z

5

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

answered Jun 28, 2016 at 2:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Scott Chamberlain · Accepted Answer · 2016-06-28 02:12:49Z

3

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.

answered Jun 28, 2016 at 2:12

Scott Chamberlain

128k37 gold badges299 silver badges448 bronze badges

Collectives™ on Stack Overflow

Using SQL parameters to DROP INDEX in C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related