where子句中的大小写 [英] Case inside where clause

问题描述

朋友们，
我试图在where子句中使用case语句，该子句决定是否应使用条件根据参数过滤数据.

我正在尝试以下操作:

Hi friends,
I was trying to use case statement inside the where clause, which decides whether the condition should be used to filter the data based on the parameter.

i was trying the following:

Select * 
FROM FWB_SF_Opportunity B 
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth  
AND A.VCID = isnull(@VCID,A.VCID)  
WHERE case when @vcid <> '''' then A.VCID = @VCID end

它给了我以下错误:

It gives me an following error:

<br />
Msg 102, Level 15, State 1, Line 35<br />
Incorrect syntax near ''=''.<br />

如何将A.VCID = @VCID条件放入case语句中

在此先感谢

How can I put A.VCID = @VCID condition inside the case statement

Thanks in advance

推荐答案

尝试一下:
这将为您工作.

Try This:
This will work for you.

FROM FWB_SF_Opportunity B left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
WHERE A.VCID = (case when @vcid <> '' then @VCID end)

>

祝你好运.

Good Luck.

嘿，尝试下面的查询:-

hey try below query:-

declare @vcid int
set @vcid= ''  --for no filter
set @vcid= 100 -- for filter
select *
FROM FWB_SF_Opportunity B left join FWB_VWSFData A 
ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
WHERE @vcid =  case @vcid  when '' then @vcid else A.VCID  end

SELECT * FROM FWB_SF_Opportunity B left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
WHERE A.VCID = ISNULL(@VCID,A.VCID)

试试这个.可能有帮助，我尚未测试.

我有另一个解决方案:-如果您的代码在过程中，则在放入代码之前，可以借助IF ELSE条件检查@VCID是否为空

Try this one. It may help, I have not tested.

I have Another solution:- If your code is inside a procedure then before puting your code you can check whether @VCID is null or not null by the help of IF ELSE condition

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