Server大小写语句错误

Question

我有一个非常简单的存储过程，我试图在Where语句中添加一个Where语句。我一直收到一条错误消息：

Msg 156，15级，状态1，过程Proc_AssuranceBilling_rpt，第36行 关键字“Case”附近的语法不正确。

我也不知道原因。谁能看看，告诉我我做错了什么？

WHERE 
   e.[DDEventDesc] IN (SELECT @rptType
           Case WHEN 'Payroll - Audit' THEN ('Payroll - Audit')
                WHEN 'Audits' Then ('Audit - Aup', 'Audit - EBP', 'Audit- Financial Institutions','Audit - Governmental','Audit - HUD','Audit - Not-for-Profit','Audit - Personal Property Tax','Audit - Single Audit','Audit - Small Business')
                WHEN 'Review & Comps' Then ('Audit - Review', 'Audit -Comp/Disc','Audit - Comp w/o Disc')
                WHEN 'Assur Tax Returns' THEN ('5500','720-PCORI','8955-SSA','Campaign Report','Corporate (1120-POL)','LM-1','LM-2','LM-3','LM-4','LM-10','LM-30','Non-Profit (990)','Non-Profit (990 EZ)','Non-Profit (990-N)','Non-Profit (990-T)','Schedule C Letters','Section 104D Letter')
           END) 
  AND AR.[ARType] = 1 
  AND (CLT.[cmaster] = 1 OR CLT.[cinvIndivEng] = 0)

Answer 1

我认为您需要将您的WHERE子句重写为：

WHERE ((@rptType = 'Payroll - Audit' AND e.[DDEventDesc] = 'Payroll - Audit') OR
      (@rptType = 'Audits' AND e.[DDEventDesc] IN ('Audit - Aup', 
                                                   'Audit - EBP', 
                                                   'Audit- Financial Institutions',
                                                   'Audit - Governmental',
                                                   'Audit - HUD',
                                                   'Audit - Not-for-Profit',
                                                   'Audit - Personal Property Tax',
                                                   'Audit - Single Audit',
                                                   'Audit - Small Business') OR
      (@rptType = 'Review & Comps' AND e.[DDEventDesc] IN  ('Audit - Review', 'Audit -Comp/Disc','Audit - Comp w/o Disc')) OR
      (@rptType = 'Assur Tax Returns' AND e.[DDEventDesc] IN ('5500','720-PCORI','8955-SSA','Campaign Report','Corporate (1120-POL)','LM-1','LM-2','LM-3','LM-4','LM-10','LM-30','Non-Profit (990)','Non-Profit (990 EZ)','Non-Profit (990-N)','Non-Profit (990-T)','Schedule C Letters','Section 104D Letter') )
   AND AR.[ARType] = 1 
   AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0)

CASE不能用于返回多个值。从MSDN到CASE

语法：

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

然后result_expression是当input_expression等于when_expression计算为TRUE或Boolean_expression计算为TRUE时返回的表达式。结果表达式是任何有效表达式。

那么什么是有效的表达式呢？根据Expressions

是Server数据库引擎计算以获得单个数据值的符号和运算符的组合。

Answer 2

变量@rptType应该是case变量。你把它放在箱子的一边

试试这个..。

      WHERE e.[DDEventDesc] IN ( SELECT Case @rptType  
WHEN 'Payroll - Audit' THEN ('''Payroll - Audit''')
      WHEN 'Audits' Then ('''Audit - Aup, Audit - EBP'', ''Audit- Financial Institutions'',''Audit - Governmental'',''Audit - HUD'',''Audit - Not-for-Profit'',''Audit - Personal Property Tax'',''Audit - Single Audit,Audit - Small Business''')
      WHEN 'Review & Comps' Then ('''Audit - Review'', ''Audit -Comp/Disc'',''Audit - Comp w/o Disc''')
      WHEN 'Assur Tax Returns' THEN ('''5500'',''720-PCORI'',''8955-SSA'',''Campaign Report'',''Corporate (1120-POL)'',''LM-1'',''LM-2'',''LM-3,''LM-4,''LM-10'',''LM-30'',''Non-Profit (990)'',''Non-Profit (990 EZ)'',''Non-Profit (990-N)'',''Non-Profit (990-T)'',''Schedule C Letters'',''Section 104D Letter''')
 END
 ) 
 AND AR.[ARType] = 1 
       AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0)