sqlserver - 如果变量满足要求，它将触发特定条件

2021-09-10 00:00:00 tsql sql-server

我正在尝试创建一个 WHERE 子句，如果变量满足特定要求，它将触发特定条件

I'm trying to create a WHERE clause that would trigger specific conditions if a variable meet specific requirements

 WHERE 
     CASE 
      WHEN @Restriction = '1' THEN CFE_EDI.ETAT = 'ENV' OR CFE_EDI.ETAT = 'OUV'
      WHEN @Restriction = '0' THEN CFE_EDI.ETAT <> 'ENV' AND CFE_EDI.ETAT <> 'OUV'
      ELSE CFE_EDI.ETAT != '' AND CFE_EDI.CODE_INSEE IS NOT NULL 
     END

如您所见，它不起作用，因为 CASE 在 THEN

As you can see, it won't work as CASE does not work well with having conditions after the THEN

我的一个朋友想出的一个解决方案是

One solution , a friend of mine came up with , would be that 

WHERE (@restriction = 1 AND CFE_EDI.ETAT IN ('ENV', 'OUV'))
OR (@restriction = 0 AND CFE_EDI.ETAT.ETAT NOT IN ('ENV', 'OUV'))
OR (CFE.EDI.ETAT <> '' AND CFE_EDI.CODE_INSEE IS NOT NULL)

然而，想想看，逻辑是如果@Restriction 等于1，那么把条件CFE_EDI.ETAT = 'ENV' OR CFE_EDI.ETAT = 'OUV' 和提出的解决方案由我的朋友不这样做.

Yet, thinking about it, the logic would be if @Restriction is equal to one then put the conditions CFE_EDI.ETAT = 'ENV' OR CFE_EDI.ETAT = 'OUV' and the solution proposed by my friend does not do that.

我知道可以在 Crystal Reports 中完成，但我不确定是否可以在 SQLServer 中完成.

I know it can be done in Crystal Reports but I'm unsure there is way of doing it in SQLServer.

谢谢

推荐答案

您的朋友解决方案已经适用于您的方案，但我不明白为什么您说它不起作用.你可以试试这个，但它类似于你朋友的解决方案.

Already your friend solution would work for your scenario but I don't why are you telling that it doesn't work. You can try this but it is similar to your friend solution.

WHERE ((@restriction = 1    AND (CFE_EDI.ETAT='ENV' OR CFE_EDI.ETAT='OUV'))
         OR (@restriction = 0   AND (CFE_EDI.ETAT<>'ENV' AND CFE_EDI.ETAT<>'OUV'))
         OR (CFE.EDI.ETAT <> '' AND CFE_EDI.CODE_INSEE IS NOT NULL))

相关文章