SQL Table Parameters

Why table params aren't allowed in SQL Server? Is there any solution to this?

Exampl开发者_StackOverflow中文版e:

using (SqlCommand myCommand = new SqlCommand("SELECT * FROM @table WHERE USERNAME=@username AND PASSWORD=HASHBYTES('SHA1',
 @password)", myConnection))
    {
        myCommand.Parameters.AddWithValue("@table", table);
        myCommand.Parameters.AddWithValue("@username", user);
        myCommand.Parameters.AddWithValue("@password", pass);

        myConnection.Open();
        SqlDataReader myReader = myCommand.ExecuteReader())
        ...................
    }

Thanks.

---

You can't paramaterise that part of the SQL. The server needs to know the name of the table to be able to 'prepare' the query, which is done before the parameters are processed.

You might dynamically generate the query, but that may open you up to SQL injection attacks and run-time SQL syntax errors. Also, there is a saving to be had if an SQL statement can be cached by the server - you'll loose that if every query is dynamically generated.

---

Why? Because the benefit of flexibility is minor compared to the nightmare it would create in query optimization and validation.

As a sidenote, even if it was recognised you'd be getting a quoted string in the SQL, not just the table name. Dynamic SQL with heavy validation is the only real way of doing this.

---

If you have to pass a table of values...

• XML parameter
• CSV (String) parameter
• Parse in SQL. See "Arrays and Lists in SQL Server 2005"

Otherwise, what are you trying to do?

Edit: I've got it now. As others mentioned, SQL does not work like that.

---

No, you cannot pass the table name as a param.

The best way would be to try using String.Format for the table name.

---

I would try to ilustrate my point of view about this with an example:

If you go to buy a car, you can "parametrize" some thinks: You can change the colour, may be some variations of the engine, you can put an MP3 or not, ... but you cant change the car model. If you change the car model, this is not a parameter, this is another car.

It is the same with sql query, the table is not a parameter is part of the sentence itself, same way that the command is (select, update) .. so you can't do @command from @table. If you change the table, this is another sentence, like the car.

(this is not a technical "because" answer for you question, but a conceptual point of view for better understanding of the techical part that others are posting)

My two cents.