T-SQL-仅当存在列表中的值时才使用AND条件 [英] T-SQL - Using AND condition only if a value from a list is present

问题描述

仅当我从预定义值列表中找到一个或多个值时，我才想在WHERE子句后的查询中添加一个AND条件，否则不应该添加条件.

I want to add an AND condition in my query after the WHERE clause only if I find (a) value(s) from a list of predefined values, otherwise the condition should not be added.

仅在参数值出现在(1001、1002、1003、1004、1005、1006、1007)列表中时，我要在此处添加的条件是"AND Table2.fieldvalue =重要值"

The condition I want to add here is "AND Table2.fieldvalue = importantvalue" only when a parameter value is present in a list of (1001, 1002, 1003, 1004, 1005, 1006, 1007)

另外，传入的参数是带有INT值的逗号分隔的STRING，但是我有一个用户定义的函数将其拆分并转换为INT

Also the parameter that comes in is a STRING with INT values comma separated but I have a user defined function to split and cast it into INT

SELECT field1,field2,field3....
from Table1 
join Table2 ON Table1.somefield = Table2.somefield
WHERE Table1.field1 = value 
AND Table2.field2 = value
AND ( CASE WHEN @parameter IN ( 1001, 1002, 1003, 1004, 1005, 1006, 1007) AND Table2.fieldvalue = importantvalue THEN 1
           ELSE 0
      END ) = 1 AND Table2.somethingelse    
GROUP BY blah,blah,blah..
HAVING blah,blah

推荐答案

在初次阅读时看起来很奇怪，但是可以.我在搜索SP中具有此功能，以仅考虑具有非NULL值的参数.当参数为非NULL时，它是来自应用程序的逗号分隔的字符串.

This looks odd at first read, but it works. I have this in a search SP to take into consideration only parameters with a non-NULL value. When the parameter is non-NULL, it is comma-separated string coming from the app.

WHERE ..................
AND (MyTable.MyColumn IN (SELECT * FROM dbo.func_SplitString(@Parameter, ',')) OR @Parameter IS NULL)

请注意，dbo.func_SplitString返回TABLE数据类型.

Note that dbo.func_SplitString returns a TABLE data type.

这篇关于T-SQL-仅当存在列表中的值时才使用AND条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！