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"
mbosicoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
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.
TheAvengerCommented:
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);
Jesse HouwingScrum Trainer | Microsoft MVP | ALM Ranger | ConsultantCommented:
SqlCommand s = new SqlCommand("SELECT * FROM _#table#_", connection2);
s.CommandText = s.CommandText.Replace ("_#table#_", vendor);

replacing is a slow process, String.Format is your friend here:

string Sql = String.Format(@"
     SELECT * FROM {0} WHERE KeyColumn = @Value
", Tablename);

SqlCommand s = new SqlCommand(Sql, connection2);

Please note that this is quite dangarous if the value isn't hardcoded or thoroughly checked as it allows SQL injection. Make sure you check the value of Tablename before actually inserting it into the SQL string.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

mbosicoAuthor Commented:
thanks for the suggestions evryone, i will test later today and get back with the results
mbosicoAuthor Commented:
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?
mbosicoAuthor Commented:
nevermind, i was being dumb

i got the correct solution

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

thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.