SQL 表参数
为什么 SQL Server 中不允许使用表参数？有什么办法解决这个问题吗？
示例：
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())
...................
}
谢谢。
Why table params aren't allowed in SQL Server? Is there any solution to this?
Example:
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您无法对 SQL 的该部分进行参数化。服务器需要知道表的名称才能“准备”查询，这是在处理参数之前完成的。
您可以动态生成查询，但这可能会让您面临 SQL 注入攻击和运行时 SQL 语法错误。此外，如果服务器可以缓存 SQL 语句，则可以节省费用 - 如果每个查询都是动态生成的，那么您将失去这一点。
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.
为什么？因为与它在查询优化和验证中造成的噩梦相比，灵活性的好处很小。
作为旁注，即使它被识别，您也会在 SQL 中得到带引号的字符串，而不仅仅是表名。具有严格验证的动态 SQL 是实现此目的的唯一真正方法。
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.
如果您必须传递值表...
否则，什么是你想做什么？
编辑：我现在已经明白了。正如其他人提到的，SQL 并不是这样工作的。
If you have to pass a table of values...
Otherwise, what are you trying to do?
Edit: I've got it now. As others mentioned, SQL does not work like that.
不，您不能将表名称作为参数传递。
最好的方法是尝试使用 String.Format 作为表名。
No, you cannot pass the table name as a param.
The best way would be to try using String.Format for the table name.
我会尝试用一个例子来说明我对此的观点：
如果你去买一辆车，你可以“参数化”一些想法：你可以改变颜色，可能是引擎的一些变化，你可以放一个MP3或不，...但你不能改变汽车型号。如果你改变汽车型号，这不是一个参数，这是另一辆车。
与sql查询相同，表不是参数而是句子本身的一部分，与命令相同（select，update）..所以你不能从@table执行@command。如果换个表，这就是另外一句话了，就像汽车一样。
（这不是对您的问题的技术“因为”答案，而是概念性的观点，以便更好地理解其他人发布的技术部分）
我的两分钱。
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.