where子句中的case语句不起作用 [英] Case statement in where clause not working

问题描述

朋友..



我正在使用存储过程，其中有一个可选参数 @equal ，默认值为NULL 。如果用户给参数赋值，则必须更改SQL查询。我试图在Where子句中使用Case。但它显示出一些错误。

帮助我正确的语法或更好的逻辑



我的要求是当用户没有为 @equal <赋值时/ b>然后

FiscalYear< @yr

Else

FiscalYear< = @ yr

Hi, Friends..

I am using stored procedure in which there is an optional parameter @equal and default value is NULL. If user give some value to the parameter SQL query has to be changed. I am trying to use Case in Where clause. but its showing some error.
Help me with proper syntax or with better logic

My requirement is When user doesn't give value for @equal then
FiscalYear<@yr
Else
FiscalYear<=@yr

Select BankCode,(SUM(Debit)-SUM(Credit)) Amt
	From vwDailyData 
	Where Pname=@ParishName and FiscalYear< (CASE WHEN @equal IS NULL THEN @yr ELSE '='+@yr END) 
	and BankCode<>'Cash'
	Group By BankCode

错误消息

将varchar值'='转换为数据类型时转换失败int。

Error Message
Conversion failed when converting the varchar value '=' to data type int.

推荐答案

你需要弄清楚细节，但这样的语法可能有效：

You will need to work out the details, but syntax like this may work:

where case when @equal IS null then @yr else null end > FiscalYear
or case when @equal IS not null then @yr else null end = FiscalYear

您不能将操作员作为案例陈述的一部分。

You cannot but the operator as part of the case statement.

SELECT
	BankCode,
	(SUM(Debit) - SUM(Credit)) Amt
FROM
	vwDailyData
WHERE
	Pname = @ParishName
	AND ((@equal IS NULL
			AND FiscalYear < @yr)
		OR (@equal IS NOT NULL
			AND FiscalYear <= @yr))
	AND BankCode <> 'Cash'
GROUP BY
	BankCode

您还可以使用IF else条件，例如

You can also use the IF else condition e.g.

IF @equal IS null
Select BankCode,(SUM(Debit)-SUM(Credit)) Amt
	From vwDailyData 
	Where Pname=@ParishName and FiscalYear< @yr
	and BankCode<>'Cash'
	Group By BankCode
ELSE
Select BankCode,(SUM(Debit)-SUM(Credit)) Amt
	From vwDailyData 
	Where Pname=@ParishName and FiscalYear<=@yr
	and BankCode<>'Cash'
	Group By BankCode

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