Problem with adding parameter

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 do I get this error and how can I f开发者_运维技巧ix it?

Parameterized queries generally deal with parameters for values within the query - not for table names, column names etc. I don't believe SQL Server supports parameterizing the table name.

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

Wrong query

select * from @name

Please correct it, after From table or view name is expected to come, you should put your param like 'select * from MyTable where col1 = @param'.

You can't put @param instead of table name. Use String.Format("select * from {0}", "MyTable"); instead.

rather than passing name in the query you can easily replace it over here

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

but this will cause the sql injection error

so i would like to suggest you go for dyanmic query execution in the sql server which you can do with SP_ExecuteSQL.