Where子句SQL SERVER中的case语句 [英] Case statement in Where Clause SQL SERVER

问题描述

我在执行sql查询时遇到错误消息156，级别15，状态1，行55

关键字'NOT'附近的语法不正确。

< pre lang =SQL> DECLARE @ param int
SET @ param = 1
选择 col1，col2，col3 来自 table1
WHERE table1.ASOfDate = ' 05/24/2013' AND （
CASE WHEN @ param = 1 那么 table1 .CloseCode NOT IN （ 2 ， 3 ， 8 ）
WHEN @ param = 2 THEN table1.CloseCode IN （ 2 ， 3 ， 8 ）
END ）

请帮帮我。

解决方案

我修改过以上查询如下，现在正在使用。



DECLARE @param int

SET @param = 1

从table1中选择col1，col2，col3

WHERE

table1.ASOfDate = '05 / 24/2013'AND（

（@param = 1 AND table1.CloseCode NOT IN（2,3,8））或

（@param = 2 AND table1.CloseCode IN（2,3,8）））

检查以下脚本...

   -   解决方案1：尝试使用动态查询 
 
  DECLARE   @ SqlQuery  < span class =code-keyword> VARCHAR （ 1000 ）， @ param   int  
 
  SELECT   @ param  =  1 ， @ SqlQuery  = ' ' 
 
  IF   @ param  =  1  
  BEGIN  
  SELECT   @ SqlQuery  = ' 从table1选择col1，col2，col3 WHERE ASOfDate =''' 05/24/2013 ' ''ANDCodeCode NOT IN（2,3,8）' 
  END  
  ELSE   IF   @ param  =  2  
  BEGIN  
  SELECT   @SqlQuery  = ' 从table1选择col1，col2，col3 WHERE ASOfDate =''' 05/24/2013 ' ''和CloseCode IN（2,3,8）' 
  END  
  EXEC （ @ SqlQuery ）
 
   -   解决方案2：更改您的WHERE Con​​diti on  
  DECLARE   @ param   int  
 
  SET   @ param  =  1  
 
 从<选择 col1，col2，col3  / span> table1 
  WHERE  table1.ASOfDate = '  05/24/2013' 
  AND （（ @ param  =  1   AND  CloseCode  NOT   IN （ 2 ， 3 ， 8 ）） OR  
（ @ param  =  2   AND  CloseCode  IN （ 2 ，  3 ， 8 ））
）

问候，

GVPrabu

同样的解决方案就像是或不是

DECLARE @param int

SET @param = 1

从table1中选择col1，col2，col3

WHERE table1.ASOfDate = '05 / 24 / 2013'AND（1 =

CASE WHER @param = 1 AND table1.CloseCode NOT IN（2,3,8）那么0

WHEN @param = 2 AND table1.CloseCode IN（2,3,8）那么0

ELSE 1 END）

I am gettinfg error while executing sql query "Msg 156, Level 15, State 1, Line 55
Incorrect syntax near the keyword 'NOT'."

DECLARE @param int 
SET @param = 1
Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013' AND(
CASE WHEN @param = 1 THEN table1.CloseCode NOT IN (2,3,8)
     WHEN @param = 2 THEN table1.CloseCode IN (2,3,8)
END)

Please help me on that.

解决方案

I modified above query as below and it is working now.

DECLARE @param int
SET @param = 1
Select col1,col2,col3 from table1
WHERE
table1.ASOfDate = '05/24/2013' AND (
(@param = 1 AND table1.CloseCode NOT IN (2,3,8) ) OR
(@param = 2 AND table1.CloseCode IN (2,3,8)))

Hi,

Check the following Script...

-- Solution 1 : Try with Dynamic Query

DECLARE @SqlQuery VARCHAR(1000), @param int 

SELECT @param = 1, @SqlQuery =''

IF @param = 1 
BEGIN
	SELECT @SqlQuery = 'Select col1,col2,col3 from table1 WHERE ASOfDate = '''05/24/2013''' AND CloseCode NOT IN (2,3,8)'
END
ELSE IF @param = 2
BEGIN
	SELECT @SqlQuery = 'Select col1,col2,col3 from table1 WHERE ASOfDate = '''05/24/2013''' AND CloseCode IN (2,3,8)'
END
EXEC (@SqlQuery)

-- Solution 2 : Change your WHERE Condition
DECLARE @param int 

SET @param = 1

Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013' 
AND( (@param = 1 AND CloseCode NOT IN (2,3,8)) OR
     (@param = 2 AND CloseCode IN (2,3,8))
   )

Regards,
GVPrabu

Also Solution Like CASE WHEN FOR IN OR NOT
DECLARE @param int
SET @param = 1
Select col1,col2,col3 from table1
WHERE table1.ASOfDate = '05/24/2013' AND( 1 =
CASE WHEN @param = 1 AND table1.CloseCode NOT IN (2,3,8) THEN 0
WHEN @param = 2 AND table1.CloseCode IN (2,3,8) THEN 0
ELSE 1 END )

这篇关于Where子句SQL SERVER中的case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！