如何使用参数和 case when 语句在两列之间进行搜索？ SQL服务器
我想根据参数上的变量在查询或表中的列之间进行搜索，例如
Declare @SelectAll as integer
Set @SelectAll = 1
Declare @Column as integer
Select mt.Column1, mtColumn2
From MyTable as mt
Where Case When @SelectAll = 1 Then
mt.Column1 IN(@Column) and mt.Column2 (' Selecting all")
When @SelectAll = 1 Then
mt.Column2 IN(@Column) and mt.Column1 (' Selecting all")
End
此查询的目的是允许用户在他们选择的列之间进行搜索。此外，参数的使用是为了编写报告服务报告的目的。
I would like to search between to columns in a s query or a table depending on the variable on a parameter e.g
Declare @SelectAll as integer
Set @SelectAll = 1
Declare @Column as integer
Select mt.Column1, mtColumn2
From MyTable as mt
Where Case When @SelectAll = 1 Then
mt.Column1 IN(@Column) and mt.Column2 (' Selecting all")
When @SelectAll = 1 Then
mt.Column2 IN(@Column) and mt.Column1 (' Selecting all")
End
The purpose of this query is to allow the user to search between the column they choose. Further more the use of parameter is for the purposes of writing reporting services reports.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你有多少列？如果不是很多，我会将所有可能的组合硬编码到存储过程中，并通过条件逻辑测试
IF (@Column = 1)等选择正确的组合。唯一的选择是使用动态我认为是SQL。尝试创建一个完成所有操作的查询最终会遇到这样的问题：在一种情况下重用执行计划会导致在另一种情况下出现性能问题。How many Columns do you have? If not many I would just hard code all of the possible combinations in to a stored procedure and select the right one with conditional logic testing
IF (@Column = 1)etc. The only alternative is to use dynamic SQL I think. Trying to create one query that does it all you will just end up with issues where reuse of the execution plan for one case causes you performance issues in another case.我找到了最适合我的解决方案
将此表达式添加到 where 子句
(@SelectAll = 1 AND mt.Column1 IN(@Column))
OR (@SelectAll = 2 AND mt.Column2 IN(@Column))
或（@SelectAll = 3）
I have found a solution that best suits me
by add this expression on the where clause
(@SelectAll = 1 AND mt.Column1 IN(@Column))
OR (@SelectAll = 2 AND mt.Column2 IN(@Column))
OR (@SelectAll = 3)