Problem with adding parameter

Question

SqlParameter param = new SqlParameter(); param.ParameterName = "@name"; param.Value = tableName; SqlCommand cmd = new SqlCommand("select * from @name", con); cmd.Parameters.Add(param); SqlDataReader rd = cmd.ExecuteReader(); 

The above code results in the following error message:

Must declare the table variable "@name".

Why am I getting this error and how to fix it?

Answer 1

Invalid request

 select * from @name 

Please fix this, after it is expected that the table or name of the From view should appear, you should put your parameter as 'select * from MyTable where col1 = @param' .

You cannot put @param in place of the table name. Use String.Format("select * from {0}", "MyTable"); instead String.Format("select * from {0}", "MyTable"); .

Answer 2

Parameterized queries usually have parameters for values ​​inside the query — not for table names, column names, etc. I do not believe that SQL Server supports parameterization of the table name.

You probably want to limit the names to a known set of valid table names (to avoid SQL injection attacks, etc.) and use the usual string replacement / formatting / whatever to build the query.

Answer 3

instead of passing the name in the request, you can easily replace it here

 string s = "select * from " + name; SqlCommand cmd = new SqlCommand(s, con); SqlDataReader rd = cmd.ExecuteReader(); 

but this will cause sql input error

therefore, I would suggest that you execute a dyanmic query on the sql server, which you can do with SP_ExecuteSQL .