未使用的 sql 参数 - 它们有什么危害吗？
考虑以下代码：
Dim sql = "SELECT * FROM MyTable WHERE value1 = @Param1"
If someCondition Then
sql = sql + " AND value2 = @Param2"
End If
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@Param1", param1Value)
cmd.Parameters.AddWithValue("@Param2", param2Value)
假设我动态构建了一个复杂的 sql 语句，该语句可能包含也可能不包含 @Param2 参数 - 将其作为参数添加到命令中是否有任何危害？
我的真实用例显然比这复杂得多，但总的来说，这是我应该避免的模式吗？如果是这样，为什么？
Consider the following code:
Dim sql = "SELECT * FROM MyTable WHERE value1 = @Param1"
If someCondition Then
sql = sql + " AND value2 = @Param2"
End If
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@Param1", param1Value)
cmd.Parameters.AddWithValue("@Param2", param2Value)
Assuming that I built a complex sql statement dynamically that may or may not have included the @Param2 parameter - is there any harm in adding it to the command as a parameter?
My real use-case is obviously far more complicated than this, but in general, is this a pattern I should avoid; and if so, why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我要注意的唯一一点是，如果您调用
.AddWithValue，则由 SQL Server 来确定参数的数据类型。SQL Server 在猜测方面做得非常好 - 但有时，它的猜测“次优”，并且提供详细信息会对您有所帮助。
所以我个人倾向于总是使用这段代码：
这有两个主要好处：
您可以轻松地将其包装在例如静态帮助器类中，或者甚至将其用作扩展方法。
这需要更多的工作，但是您可以获得更多的控制权，并且如果您让 SQL Server 来猜测您的类型，则可能会避免不必要的、耗时的数据类型转换和其他意外的副作用。
The only point I would take note is the fact that if you call
.AddWithValue, you leave it up to SQL Server to figure out what the data type of the parameter will be.SQL Server does a remarkably good job of guessing - but sometimes, it gets it "sub-optimally" and it would be helpful for you to provide the details.
So I personally tend to always use this snippet of code:
This has two main benefits:
You could easily wrap this in a e.g. static helper class, or even use it as an extension method.
It's a tad more work, but you get more control, and might avoid unnecessary, time-consuming datatype conversions and other unexpected side effects if you leave it up to SQL Server to guess your types.
避免传递不需要的参数始终是最佳实践。如果您传入一个没有值的参数，那么它要么会假设您正在寻找 NULL、空字符串，要么会给您一个错误，指出它无法处理该请求。
It is always a best practice to avoid passing in parameters that are not needed. If you pass in a parameter that does not have a value then it is either going to assume that you are looking for a NULL, empty string, or it will give you an error that it cannot process the request.