5

I am trying to pass a table name as a parameter to my query through SqlCommand but it doesn't seems to be working. Here is my code;

SqlConnection con = new SqlConnection( "server=.;user=sa;password=12345;database=employee" );
con.Open( );
SqlCommand cmd = new SqlCommand( "drop table @tbName" , con );
cmd.Parameters.AddWithValue( "@tbName" , "SampleTable" );
cmd.ExecuteNonQuery( );
con.Close( );
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Uthaiah
  • 1,283
  • 13
  • 14

2 Answers2

10

SqlCommand.Parameters are supported for Data manipulation language operations not Data definition language operations.

Even if you use DML, you can't parameterize your table names or column names etc.. You can parameterize only your values.

Data manipulation language = 

SELECT ... FROM ... WHERE ...
INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...

Data definition language =

CREATE TABLE ... 
DROP TABLE ... ;
ALTER TABLE ... ADD ... INTEGER;

You can't use DROP statement with parameters.

If you really have to use drop statement, you might need to use string concatenation on your SqlCommand. (Be aware about SQL Injection) You might need to take a look at the term called Dynamic SQL

Also use using statement to dispose your SqlConnection and SqlCommand like;

using(SqlConnection con = new SqlConnection(ConnectionString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = "drop table " + "SampleTable";
   con.Open()
   cmd.ExecuteNonQuery();
}
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
  • This isn't really a question of DML vs DDL: it would be equally problematic to insert a table name or a column name into a SELECT statement. SQL Parameters are simply limited to places you could put variables in SQL, and you can't use variables when referring to database objects. – StriplingWarrior Jun 04 '21 at 23:05
3

User Soner Gönül pointed out why it doesn't work, nevertheless you can write stored procedure yourself.

CREATE PROCEDURE dbo.procdroptable
    @TABLENAME SYSNAME
AS
 BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
    EXEC sp_executesql @SQL;
 END
GO

Code from this question.

Community
  • 1
  • 1
Sriram Sakthivel
  • 72,067
  • 7
  • 111
  • 189