Admit Card Procedure

ALTER proc [dbo].[sp_AdmitCard]
(
@TermId varchar(50)
)
as
— exec sp_AdmitCard ‘CSE2012FEB_2K11Y1T1′
begin
DECLARE @StudentId int, @CourseId varchar(50), @TheoryCourse varchar(500),
@SessionalCourse varchar(500), @CourseCode varchar(50), @CourseType varchar(50),
@StudentName varchar(100)

SET @TheoryCourse=” SET @SessionalCourse=”

———————————- Temporary Table ————————————
declare @AdmitCard table
(
StudentId int,
StudentName varchar(100),
StudentPicture image,
TheoryCourses varchar(500),
SessionalCourses varchar(500)
)
declare @Tabstring nvarchar(max)
declare @Tabname nvarchar(100)
set @Tabname=’tblAdmitCard’
set @Tabstring=’create table #’ + @Tabname
——————————————————————————————–

—————————— Cursor —————————————————–
declare AdmitCardGeneration cursor for

select DISTINCT StudentId from T_Term_Std_Course_Reg WHERE TermId= @TermId
GROUP BY StudentId

open AdmitCardGeneration

fetch next from AdmitCardGeneration into
@StudentId

while @@FETCH_STATUS = 0
begin

SELECT @StudentName=StudentsName
FROM T_Std_General_Information WHERE StudentId=@StudentId

declare AdmitCard cursor for
SELECT T_Term_Course_Offer.CourseCode,T_Term_Course_Offer.CourseType
FROM T_Term_Std_Course_Reg LEFT OUTER JOIN
T_Term_Course_Offer ON T_Term_Std_Course_Reg.TermId = T_Term_Course_Offer.TermId AND
T_Term_Std_Course_Reg.CourseId = T_Term_Course_Offer.CourseId
WHERE T_Term_Std_Course_Reg.StudentId=@StudentId AND T_Term_Std_Course_Reg.TermId= @TermId

open AdmitCard
fetch next from AdmitCard into
@CourseCode, @CourseType
while @@FETCH_STATUS = 0
begin

if(@CourseType=’Theory’)
begin
SET @TheoryCourse=@TheoryCourse+@CourseCode+’, ‘
end
else
begin
SET @SessionalCourse=@SessionalCourse+ @CourseCode+’, ‘
end
fetch next from AdmitCard into
@CourseCode, @CourseType
end
close AdmitCard
deallocate AdmitCard

SET @TheoryCourse = LEFT(@TheoryCourse,LEN(@TheoryCourse)-1)
SET @SessionalCourse = LEFT(@SessionalCourse,LEN(@SessionalCourse)-1)

————————– Insert Into Temporary Table ———————————————-
insert into @AdmitCard
(
StudentId,
StudentName,
StudentPicture,
TheoryCourses,
SessionalCourses
)
VALUES(@StudentId,@StudentName,(SELECT StudentPicture FROM T_Std_General_Information WHERE StudentId=@StudentId),@TheoryCourse,@SessionalCourse)
————————————————————————————————————
SET @TheoryCourse=” SET @SessionalCourse=”

fetch next from AdmitCardGeneration into
@StudentId
end

close AdmitCardGeneration
deallocate AdmitCardGeneration

SELECT * FROM @AdmitCard
end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s