Procedure simplification - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Procedure simplification

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-07-23 : 08:48:04

Hi all,

I have created procedure.It is working fine. Is there any possibility to improve my query.. My query is

Alter Procedure EnrollMentByInstructor
@FromYear int,
@ToYear int,
@CompanyId varchar(100)
As
Begin

DECLARE @cmd nvarchar(max)
DECLARE @colcount int
SET @colcount = @ToYear

Create Table #tmpFinal (Instructor varchar(200),Instrument varchar(200))
DECLARE @counter int

SET @counter = @FromYear-1
WHILE @counter < @colcount
BEGIN
SET @counter = @counter + 1
SET @cmd = 'ALTER TABLE #tmpFinal ADD Year' + CAST(@counter AS varchar(20)) + ' NVARCHAR(MAX)'
EXEC(@cmd)
END
SET @cmd = 'ALTER TABLE #tmpFinal ADD TotalCount int'
EXEC(@cmd)

Create Table #Temp11(Instructor varchar(200),Instrument varchar(200),StudentId bigint,Year int)
insert into #Temp11
select (T.first_name+' '+T.last_name) as Instructor,ID.instrument_name as Instrument,Ses.StudentID,Year(Ses.Date) from class as C
Left Join InstrumentDetails as ID on ID.UID=C.Instrument
Left Join Teacher as T on T.UID=C.Teacher
Left Join class_students as CS on CS.ClassId=C.ClassId
Left Join Class_session as Ses on Ses.ClassID=C.ClassId and Ses.StudentId=Cs.Student_Id
where C.CompanyId in (select * from Split(@CompanyId,','))
group by(T.first_name+' '+T.last_name),ID.instrument_name,C.ClassId,
Year(Ses.Date),Ses.StudentId

Create Table #Temp12(Instructor varchar(200),Instrument varchar(200),StudentCount bigint,Year int)
insert into #Temp12
select Instructor,Instrument,Count(StudentId) as StudentCount,Year from #Temp11
group by Instructor,Instrument,Year
Create table #temp1(Instructor varchar(200),Instrument varchar(200),StudentCount int,Year1 int)
insert into #temp1
select * from #Temp12

insert into #tmpFinal(Instructor,Instrument)
select Instructor,Instrument from #temp1

DECLARE @Rowcount int
DECLARE @counterTotal float
DECLARE @counter2 int
DECLARE @counterTot2 int
DECLARE @Rowcount1 int
SET @Rowcount1=0
SET @counterTot2=0
select @Rowcount=@@RowCount from #temp1
select @Rowcount=@@RowCount from #temp1

WHILE @Rowcount > @Rowcount1
BEGIN
Set @Rowcount1=@Rowcount1+1

DECLARE @cmd1 nvarchar(max)
DECLARE @cmd2 nvarchar(max)
DECLARE @counter1 int
DECLARE @Column1 varchar(200),@Column2 varchar(200)
Select top(1) @Column1=Instructor,@Column2=Instrument from #temp1

SET @counter1 = @FromYear-1
DECLARE @colcount1 int
SET @colcount1 = @ToYear
WHILE @counter1 < @colcount1
BEGIN
--select @PayMode1
SET @counter1 = @counter1 + 1
SET @cmd1 = 'Update #tmpFinal set Year' + CAST(@counter1 AS varchar(20)) + ' =(select StudentCount from #temp1 where Year1='+ CAST(@counter1 AS varchar(20)) +' and Instructor='''+CAST(@Column1 AS varchar(20))+''' and Instrument='''+CAST(@Column2 AS varchar(20))+''') where Instructor='''+CAST(@Column1 AS varchar(20))+''' and Instrument='''+CAST(@Column2 AS varchar(20))+''''
select @counterTotal=StudentCount from #temp1 where Year1= CAST(@counter1 AS varchar(20)) and Instructor=CAST(@Column1 AS varchar(20)) and Instrument= CAST(@Column2 AS varchar(20))
if @counterTotal is not null
Begin
set @counterTot2=@counterTot2+@counterTotal
End
Else
Begin

set @counterTot2=@counterTot2
End
Set @counterTotal=0

Update #tmpFinal set TotalCount=@counterTot2 where Instructor= CAST(@Column1 AS varchar(20)) and Instrument=CAST(@Column2 AS varchar(20))

EXEC(@cmd1)
END
set @counterTot2=0

Delete Top(1) from #temp1
END

select * from #tmpFinal

End

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-23 : 09:46:56

Yes it can be improved but you'd need to explain what you're trying to accomplish. It looks like you are dynamically pivoting years of student enrollment. Please supply some sample data and expected output.

If you are using this output in a report, you are much better off letting the reporting package do the pivoting. If you absolutely have to do it using SQL Server, look at this technique instead:

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources