asked on

how to use a SqlCommand parameter for a table name

I would like to use a parameter for the table name in the select statment of the SqlCommand. I have :

SqlCommand s;
s = new SqlCommand("SELECT * FROM @table",connection2);
s.Parameters.Add("@table",SqlDbType.VarChar,4).Value = vendor;

But it doesnt seem to like that and errors out? It says must declare the variable @table

If i have it like:

s.Parameters.Add("table",SqlDbType.VarChar,4).Value = vendor;

It errors out and says "incorrect syntax near "table"

Fernando Soto

Parameters can be input, output or both but not a table name. When creating a table you have the name of the table, column, and table constraints but a table does not have a data type such as VarChar.

TheAvenger

As FernandoSoto said, you cannot use a paramater for the table name. However you can use a trick like this to somehow parameterize you query:

SqlCommand s = new SqlCommand("SELECT * FROM _#table#_", connection2);

s.CommandText = s.CommandText.Replace ("_#table#_", vendor);

ASKER CERTIFIED SOLUTION

Jesse Houwing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbosico

ASKER

thanks for the suggestions evryone, i will test later today and get back with the results

mbosico

ASKER

is this not the correct format:

s = new SqlCommand(String.Format(@"SELECT * FROM {0} WHERE KeyColumn = @Value", "Master_" + vendor),connection2);

says i have to declare @Value?

mbosico

ASKER

nevermind, i was being dumb

i got the correct solution

s = new SqlCommand(String.Format(@"SELECT * FROM {0}", "Master_" + vendor),connection2);

thanks