syntax error in SQL Stored procedure

[Krishnakanth]

CASE budgetcategory
WHEN 'ECP' THEN 1
WHEN 'CAP' THEN 2
ELSE 3
END as SortVal1

CASE ProjectType
WHEN 'PROJ' THEN 1
WHEN 'IDF' THEN 2
WHEN 'CLNUP' THEN 3
WHEN 'REBL' THEN 4
WHEN 'R&R' THEN 5
WHEN 'RTDP' THEN 6
WHEN 'CAP' THEN 7
ELSE 8
END as SortVal2


The SQL server is throwing some error message while trying to add "END as
SortVal1" and "END as SortVal2". I am writing stored procedure in SQL server
2000. I have to give this SortVal1 and SortVal2 (sorting and grouping) in MS
Access 2000 for report generation. The MS Access will pass values to the
stored procedure and in turn report will be generated.
The problem is I need to sort the field ProjectType as follows.
PROJ, IDF, CLNUP, REBL, R&R, RTDP, CAP.

I have given the stored procedure as follows. Please help us to fix this
problem.

-------------
Create Procedure both_str_proc_v12
@relyr char(4),
@relno char(2),
@dotno char(1),
@budget57 char(6),
@budget58 char(6),
@budget59 char(6),
@region78 char(15),
@region80 char(15),
@region82 char(15),
@region84 char(15),
@status98 char(1),
@status100 char(1)
as
BEGIN
SELECT dbo.RSM_Project.LOB, dbo.RSM_Project.ProjScope,
dbo.RSM_Project.RelYear, dbo.RSM_Project.ProjectType, dbo.RSM_Project.RelNum,
dbo.RSM_Project.DotNum, dbo.RSM_Project.BudgetCategory,
dbo.RSM_Project.ProjSeqNum, dbo.RSM_Project.ProjectId,
dbo.RSM_Project.ProjectTitle,
PCF=(dbo.RSM_Project.BizPCFImpact+'/'+dbo.RSM_Project.ITPCFImpact),
APPS = SUM(APPSSME+APPSDev+APPSSMEICF+APPSDevICF),
AutoBatch=SUM(AutobatchSME+AutobatchDev+AutobatchSMEICF+AutobatchDevICF),
AutoEdge=SUM(AutoEDGESME+AutoEDGEDev+AutoEDGESMEICF+AutoEDGEDevICF),
FireBatch =SUM(FirebatchSME+FirebatchDev+FirebatchSMEICF+FirebatchDevICF),
eAuto=SUM(eAutoSME+eAutoDev+eAutoSMEICF+eAutoDevICF),
eHome =SUM(eHomeSME+eHomeDev+eHomeSMEICF+eHomeDevICF),
FPPS=SUM(FPPSSME+FPPSDev+FPPSSMEICF+FPPSDevICF+CDJSME+CDJDev+CDJSMEICF+CDJDevICF+FireNetworkSME+FireNetworkDev+FireNetworkSMEICF+FireNetworkDevICF),
FireEdge=SUM(FireEDGESME+FireEDGEDev+FireEDGESMEICF+FireEDGEDevICF),
Tables =SUM(TablesSME+TablesDev+TablesSMEICF+TablesDevICF),
Total= SUM(APPSSME+APPSDev+APPSSMEICF+APPSDevICF+
AutobatchSME+AutobatchDev+AutobatchSMEICF+AutobatchDevICF+
AutoEDGESME+AutoEDGEDev+AutoEDGESMEICF+AutoEDGEDevICF+
FirebatchSME+FirebatchDev+FirebatchSMEICF+FirebatchDevICF+
eAutoSME+eAutoDev+eAutoSMEICF+eAutoDevICF+
FPPSSME+FPPSDev+FPPSSMEICF+FPPSDevICF+CDJSME+CDJDev+CDJSMEICF+CDJDevICF+FireNetworkSME+FireNetworkDev+FireNetworkSMEICF+FireNetworkDevICF+
eHomeSME+eHomeDev+eHomeSMEICF+eHomeDevICF+
FireEDGESME+FireEDGEDev+FireEDGESMEICF+FireEDGEDevICF+
TablesSME+TablesDev+TablesSMEICF+TablesDevICF),
dbo.RSM_Project.Emergency, dbo.RSM_Project.ProjCategory,
dbo.RSM_Project.BRISubmittedBy, dbo.RSM_Project.ITSME,
CEA =SUM(CEASME+CEADEV+CEASMEICF+CEADEVICF),
Region=dbo.RSM_ProjectState.Region+space(10),
dbo.RSM_Project.ProjStatus
INTO #RptResult
FROM dbo.RSM_Project, dbo.RSM_ProjectROM, dbo.RSM_ProjectState
WHERE dbo.RSM_Project.ProjSeqNum = dbo.RSM_ProjectROM.ProjSeqNum AND
dbo.RSM_Project.ProjSeqNum *= dbo.RSM_ProjectState.ProjSeqNum AND
(((dbo.RSM_Project.RelYear)= @relyr) AND
((dbo.RSM_Project.RelNum)= @relno) AND
((dbo.RSM_Project.DotNum)= @dotno) AND
((dbo.RSM_Project.BudgetCategory)= @budget57 Or
(dbo.RSM_Project.BudgetCategory)= @budget58 Or
(dbo.RSM_Project.BudgetCategory)= @budget59) AND
((dbo.RSM_ProjectState.Region)= @region78 Or
(dbo.RSM_ProjectState.Region)= @region80 Or
(dbo.RSM_ProjectState.Region)= @region82 Or
(dbo.RSM_ProjectState.Region)= @region84) AND
((dbo.RSM_Project.ProjStatus)= @status98 Or
(dbo.RSM_Project.ProjStatus)= @status100) AND
((dbo.RSM_Project.BudgetCategory) in ('BASE','ECP','CAP')) AND
((dbo.RSM_Project.Projecttype) in
('RTDP','R&R','CLNUP','REBL','PROJ','IDF','CAP','PST')))
GROUP BY dbo.RSM_Project.LOB,
dbo.RSM_Project.ProjScope,
dbo.RSM_Project.RelYear,
dbo.RSM_Project.ProjectType,
dbo.RSM_Project.RelNum,
dbo.RSM_Project.DotNum,
dbo.RSM_Project.BudgetCategory,
dbo.RSM_Project.ProjSeqNum,
dbo.RSM_Project.ProjectId,
dbo.RSM_Project.ProjectTitle,
dbo.RSM_Project.BizPCFImpact,
dbo.RSM_Project.ITPCFImpact,
dbo.RSM_Project.Emergency,
dbo.RSM_Project.ProjCategory,
dbo.RSM_Project.BRISubmittedBy,
dbo.RSM_Project.ITSME,
dbo.RSM_ProjectState.Region,
dbo.RSM_Project.ProjStatus
ORDER BY (dbo.RSM_Project.BudgetCategory) DESC,
case
when LOB = 'A' then 1
when LOB = 'B' then 2
when LOB = 'F' then 3
end,
case
when Projecttype = 'PROJ' then 1
when Projecttype = 'IDF' then 2
when Projecttype = 'CLNUP' then 3
when Projecttype = 'REBL' then 4
when Projecttype = 'R&R' then 5
when Projecttype = 'RTDP' then 6
when Projecttype = 'CAP' then 7
when Projecttype = 'PST' then 8
end

DECLARE @ResultCnt int, @ResCounter int, @ProjSeqNum int, @RegionCnt int,
@RegionCounter int, @AllRegions varchar(30), @Region varchar(10)

Select * into #temp from #RptResult
Select @ResultCnt = count(*) from #temp
Set Rowcount 1
Select @ResCounter = 0
WHILE @ResCounter <= @ResultCnt
BEGIN
Select @ProjSeqNum = ProjSeqNum FROM #temp
set rowcount 0
IF (Select Count(*) FROM RSM_ProjectState WHERE ProjSeqNum = @ProjSeqNum
and Region is Not Null) > 0
BEGIN
Select Region into #region from RSM_ProjectState WHERE ProjSeqNum =
@ProjSeqNum and Region is Not Null
Select @RegionCnt = Count(*) FROM #region
set rowcount 1
Select @RegionCounter = 1, @AllREgions = ''
IF @RegionCnt = 1 ---- Handles ONE region ----------------
BEGIN
Select @AllRegions = Region from #region
END
ELSE
BEGIN ------ Handles Multiple Regions ------------------
While @RegionCounter <= @RegionCnt
BEGIN
Select @Region = ltrim(rtrim(Region)) FROM #region
IF IsNull(charindex(@Region,@AllRegions),'0') = 0
BEGIN
IF @RegionCounter = 1
BEGIN SELECT @AllRegions = @Region END
ELSE
BEGIN Select @AllRegions = IsNull(@AllRegions,'') +
',' + @Region END
END
SELECT @RegionCounter = @RegionCOunter + 1
Delete from #region
END
UPDATE #RptResult
Set Region = @AllRegions
Where ProjseqNum = @ProjSeqNum
END
Drop Table #region
END
Set Rowcount 1
SELECT @ResCounter = @ResCounter + 1
Delete from #temp
END

Set rowcount 0
SELECT * from #RptResult

--Drop table #temp, #RptResult
END
GO
------------------------