create PROCEDURE dbo.SPEnrollByCampus2YearsTotal

@CurrYear char(8),

@PrevYear char(8),

@ViewName Varchar(50)

AS

Exec("select L.LocName as LocationName,

sum(case

when V.code_ofcl_univ_yr_sem ='" + @CurrYear + "' then (V.numb_ofcl_pers_id)

end) as TotalCurr,

sum(case

when V.code_ofcl_univ_yr_sem ='" + @PrevYear + "' then (V.numb_ofcl_pers_id)

end) as TotalPrev

into ##TempYears

from Location L,"+@ViewName+" V

where L.LocCode = V.code_ofcl_stud_report_loc and L.LocCode <> 'UP'

and L.LocCode<>'HY' and L.LocCode<>'DN'--v.code_ofcl_stud_report_loc <> 'UP'

and v.code_ofcl_stud_report_loc <> 'HY' and v.code_ofcl_stud_report_loc <> 'DN' -- and C.OtherAreas='0'

Group By L.LocName

order by L.LocName")

select * from [##TempYears]

compute Sum(TotalCurr),sum(TotalPrev)

drop table [##TempYears]