|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2007-02-09 : 13:11:57
|
| i have a SP like below... I am keep getting an error in the case statment: Incorrect syntax near '='.what am i doing wrong?alter proc [dbo].[usp_Cumulativeconsolidatedreport] ( @year int , @region int = 1000 , @month int )ASdeclare @prev_month intdeclare @prev_year intSET @prev_month = MONTH(DATEADD(month, @month , DATEADD(year, @year - 1900, 0)))SET @prev_year = YEAR(DATEADD(month, @month , DATEADD(year, @year - 1900, 0)));select sum(isnull(bad_dept_provision ,0) ) as badp ,a.month,c.region as Region_num,b.region_num as region, b.unit_name ,b.unit_abbr as unit_abbr, a.financial_class_codeFrOM [Revenue_Aggregate_Assertions] a join Revenue_aggregate b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_codeleft join regions c on b.region_num = c.region_numwhere ( ((b.month < @prev_month and b.year = @prev_year ) or (b.year < @prev_year )) ) and case when @region= 1000 then (@region = 1000 or b.region_num = @region) when @region = 998 then b.region_num in ( 5,6,7,8) when @region = -1 then b.unit_abbr in ( 'B01','B02','B03','B04','B05','B06','B07') when @region = 999 then b.unit_abbr not in ( 'B01','B02','B03','B04','B05','B06','B07') else (b.region_num = @region) end group by a.month,c.region, b.unit_name ,c.region, b.region_num ,b.unit_abbr,a.financial_class_code) a |
|
CASE in where statement