CREATE PROCEDURE dbo.SPEnrollByCollege2YearsHYTotal

@CurrYear char(8),

@PrevYear char(8),

@ViewName Varchar(50)

AS

Exec("Select V.code_ofcl_stud_sem_clsf as DegreeName,

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,L.LocCode

Into ##TempYears

From Location L, "+@ViewName +" V

Where L.LocCode = V.code_ofcl_stud_report_loc

and ( V.code_ofcl_stud_report_loc = 'HY'

and (V.Code_ofcl_stud_sem_clsf = 'MD'

or V.Code_ofcl_stud_sem_clsf = 'GR'))

Group By L.LocCode,V.code_ofcl_stud_sem_clsf Order by V.code_ofcl_stud_sem_clsf desc")

select * from [##TempYears]

where LocCode = 'HY'

compute Sum(TotalCurr),sum(TotalPrev)

drop table [##TempYears]