February 25, 2011 at 1:22 am
Hi Friends,
I am Working On School Management System Software in which i need to display core marks register of a particular class to accomplish this task i made a stored procedure
like this:
USE [dpsbuland]
GO
/****** Object: StoredProcedure [dbo].[Core_Register1] Script Date: 02/25/2011 12:33:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Core_Register1] (@class_id int ,@session varchar(50),@term varchar(50))
as
begin
SET NOCOUNT ON
declare @subjectName varchar(500)
declare @subjectid varchar(50)
declare @count_Grade int
drop table TB_CoreRegister
declare @sum_field varchar(500)
declare @sum_field_total varchar(500)
set @sum_field=''
set @sum_field_total=''
create table TB_CoreRegister (stu_id int,stu_name varchar(500),session varchar(500),term varchar(500))
insert into TB_CoreRegister (Stu_id,stu_name,session,term)values(-1,'0',@session,@term )
insert into TB_CoreRegister (Stu_id,stu_name,session,term)values(-2,'0',@session,@term )
insert into TB_CoreRegister (Stu_id,stu_name,session,term)(select stu_id,last_name+' '+first_name+' '+mid_name,@session,@term from TB_StudentMaster where class_id=@class_id)
--select @count_Grade=count(*) from TB_Exam_Max_Min te join tb_subjectMaster ts on ts.id=te.subject_id where te.c_id=(select c_id from tb_class where class_id=@class_id) and session=@session and grade_subject='Y'
declare new_cursor CURSOR
for
select distinct ts.subject,te.subject_id from TB_Exam_Max_Min te join tb_subjectMaster ts on ts.id=te.subject_id where te.c_id=(select c_id from tb_class where class_id=@class_id) and session=@session and grade_subject='N'
open new_cursor
fetch next from new_cursor into @subjectName,@subjectid
while(@@fetch_status<>-1)
begin
if(@@fetch_status<>-2)
begin
exec('alter table TB_CoreRegister add ['+@subjectName+'_FA I] varchar(500)')
exec('update TB_CoreRegister set ['+@subjectName+'_FA I]'+'= ts.[Core_FA I] from TB_CoreRegister t join TB_RESULT_TERMI_CORE ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+@subjectName+'_FA I]'+'=''FA I'' where stu_id=-2')
declare @weightage varchar(50)
select @weightage=weightage from dbo.TB_WeightageMarks where session=@session and c_id =(select c_id from tb_class where class_id=@class_id) and exam_id=1 and exc_id=1 and term=@term
exec('update TB_CoreRegister set ['+@subjectName+'_FA I]'+'=''FA I'''+'+ '' ['+@weightage+'] '' where stu_id=-2')
exec('alter table TB_CoreRegister add [' +@subjectName+'_FA II] varchar(500)')
exec('update TB_CoreRegister set ['+ @subjectName+'_FA II]'+'=ts.[Core_FA II] from TB_CoreRegister t join TB_RESULT_TERMI_CORE ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+ @subjectName+'_FA II]'+'=''FA II'' where stu_id=-2')
select @weightage=cast(weightage as varchar(50)) from dbo.TB_WeightageMarks where session=@session and c_id =(select c_id from tb_class where class_id=@class_id) and exam_id=2 and exc_id=3 and term=@term
exec('update TB_CoreRegister set ['+@subjectName+'_FA II]'+'=''FA II'''+' +'' ['+@weightage+'] '' where stu_id=-2')
--
exec('alter table TB_CoreRegister add [' +@subjectName+'_Summative I] varchar(500)')
exec('update TB_CoreRegister set ['+ @subjectName+'_Summative I]'+'=ts.[Core_SA I] from TB_CoreRegister t join TB_RESULT_TERMI_CORE ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+ @subjectName+'_Summative I]'+'=''SA I'' where stu_id=-2')
select @weightage=cast(weightage as varchar(50)) from dbo.TB_WeightageMarks where session=@session and c_id =(select c_id from tb_class where class_id=@class_id) and exam_id=3 and exc_id=9 and term=@term
exec('update TB_CoreRegister set ['+@subjectName+'_Summative I]'+'=''SA I'''+' +'' ['+@weightage+'] '' where stu_id=-2')
--exec('update TB_Class_result set ['+ @subjectName+'_Summative I]'+'=upper('''+@subjectName+''') where stu_id=-1')
exec('update TB_CoreRegister set ['+@subjectName+'_Summative I]'+'=upper('''+@subjectName+''') where stu_id=-1')
exec('alter table TB_CoreRegister add [' +@subjectName+'_Total] varchar(500)')
exec('update TB_CoreRegister set ['+ @subjectName+'_Total]'+'= ts.[total_core] from TB_CoreRegister t join TB_RESULT_TERMI_CORE ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+ @subjectName+'_Total]'+'= 0 from TB_CoreRegister where ['+ @subjectName+'_Total] is null and stu_id!=-2')
exec('update TB_CoreRegister set ['+ @subjectName+'_Total]'+'=''Total'' where stu_id=-2')
IF(@sum_field!='')
begin
set @sum_field=@sum_field+'+cast(['+@subjectName+'_Total] as numeric(12,2))'
end
else
begin
set @sum_field=@sum_field+'cast(['+@subjectName+'_Total] as numeric(12,2))'
end
exec('alter table TB_CoreRegister add [' +@subjectName+'_Grade] varchar(500)')
exec('update TB_CoreRegister set ['+ @subjectName+'_Grade]'+'=ts.[Grade] from TB_CoreRegister t join TB_RESULT_TERMI_CORE ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+ @subjectName+'_Grade]'+'=''Grade'' where stu_id=-2')
exec('alter table TB_CoreRegister add [' +@subjectName+'_FTotal] varchar(500)')
exec('update TB_CoreRegister set ['+ @subjectName+'_FTotal]'+'=ts.total_out_of from TB_CoreRegister t join TB_RESULT_TERMI_CORE ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+ @subjectName+'_FTotal]'+'=0 from TB_CoreRegister where ['+ @subjectName+'_FTotal] is null and stu_id!=-2')
exec('update TB_CoreRegister set ['+ @subjectName+'_FTotal]'+'=''Total Marks'' where stu_id=-2')
IF(@sum_field_total!='')
begin
set @sum_field_total=@sum_field_total+'+cast(['+@subjectName+'_FTotal] as numeric(12,2))'
end
else
begin
set @sum_field_total=@sum_field_total+'cast(['+@subjectName+'_FTotal] as numeric(12,2))'
end
end
fetch next from new_cursor into @subjectName,@subjectid
end
close new_cursor
deallocate new_cursor
declare new_cursor CURSOR
for
select distinct ts.subject,te.subject_id from TB_Exam_Max_Min te join tb_subjectMaster ts on ts.id=te.subject_id where te.c_id=(select c_id from tb_class where class_id=@class_id) and session=@session and grade_subject='Y'
open new_cursor
fetch next from new_cursor into @subjectName,@subjectid
while(@@fetch_status<>-1)
begin
if(@@fetch_status<>-2)
begin
exec('alter table TB_CoreRegister add ['+@subjectName+'_Grade] varchar(500)')
--select grade from tb_gradeExam where session='2008 - 2009' and term ='Term I' and subject_id=10
exec('update TB_CoreRegister set ['+@subjectName+'_Grade]'+'=ts.grade from TB_CoreRegister t join tb_gradeExam ts on ts.stu_id=t.stu_id and ts.subject_id='''+@subjectid+''' and ts.term='''+@term+''' and ts.session='''+@session+'''')
exec('update TB_CoreRegister set ['+ @subjectName+'_Grade]'+'=upper('''+@subjectName+''') where stu_id=-1')
exec('update TB_CoreRegister set ['+ @subjectName+'_Grade]'+'=''GRADE'' where stu_id=-2')
end
fetch next from new_cursor into @subjectName,@subjectid
end
close new_cursor
deallocate new_cursor
exec('alter table TB_CoreRegister add [Grand_total] varchar(500)')
exec('update TB_CoreRegister set [Grand_total]='+@sum_field+' from TB_CoreRegister where stu_id not in(-1,-2)')
exec('update TB_CoreRegister set [Grand_total]=''Total'' from TB_CoreRegister where stu_id=-2')
exec('alter table TB_CoreRegister add [Grand_total_outof] varchar(500)')
exec('update TB_CoreRegister set [Grand_total_outof]='+@sum_field_total+' from TB_CoreRegister where stu_id not in(-1,-2)')
exec('update TB_CoreRegister set [Grand_total_outof]=''Total Out Of'' from TB_CoreRegister where stu_id=-2')
exec('alter table TB_CoreRegister add [Final_Marks] varchar(500)')
exec('update TB_CoreRegister set [Final_Marks]=[Grand_total]+''/''+cast(cast([Grand_total_outof] as numeric(12,0)) as varchar(50)) from TB_CoreRegister where stu_id not in(-1,-2)')
exec('update TB_CoreRegister set [Final_Marks]=''Total Out Of'' from TB_CoreRegister where stu_id=-2')
exec('alter table TB_CoreRegister add [Final_Grade] varchar(500)')
exec('update TB_CoreRegister set [Final_Grade]=(select grade from chk_grade where m_range_from=(select top 1 m_range_from from chk_grade where m_range_from<=[Grand_total])) from TB_CoreRegister where stu_id not in(-1,-2)')
exec('update TB_CoreRegister set [Final_Grade]=''Final Grade'' from TB_CoreRegister where stu_id=-2')
exec('ALTER TABLE TB_CoreRegister DROP COLUMN grand_total')
exec('ALTER TABLE TB_CoreRegister DROP COLUMN grand_total_outof')
--print (cast(@sum_field as varchar(500)))
--exec('select *,'+@sum_field+' from TB_CoreRegister order by stu_name')
select * from TB_CoreRegister r order by stu_name
end
But when i execute this stored procedure it will return result approx 7 or 8 minuts.
how to improve performance of this type of stored procedure.
February 25, 2011 at 1:37 am
Im assuming that this is a homework assignment!
This is quite a convoluted mess.
Altering tables ,and the excessive amounts of dynamic sql, as you have done is not something you should be doing as a matter of course.
Start again , and think simply about the data you have and what you are trying to achieve.
February 25, 2011 at 5:47 am
yeah that looks like homework. If it is, ask your classmates or teacher.
drop the cursors and rethink your approach.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply