how to improve my query.

[yan19454]

/*. according the due date end user select and ALTER temp table
find the nomatch table in the occh and return the table
if the end user select the data, then it will show just the empl take the
test during the period.
otherise including all the empl not take the test.
1. test a.with previous year training and current year training
b. no previous year training record and current year traning year
c. with previous traning record and no current year training.
*/

create PROCEDURE dbo.DueListWithPreviousTraining
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@unit nchar(5) = NULL,
@cc nvarchar(4000) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)

begin

CREATE TABLE #tempPreviousWinTrainingLog (
[EmplNO] [nvarchar] (50) ,
[CTDate] [datetime] NULL ,
[QuizName] [char] (25)
)



set @sql='SELECT distinct EmplNO , QuizName, CTDate
FROM tblPreviousWinTrainingLog b
WHERE CTDate IN (
SELECT MAX(CTDate)
FROM tblPreviousWinTrainingLog a
WHERE b.EmplNO = a.EmplNO group by QuizName) '
IF @quiz IS NOT NULL and len(@quiz)<>0
set @sql = @sql + ' AND b.QuizName =''' + @quiz +''''
IF @fromdate IS NOT NULL
begin
set @fromdate= DATEADD(year,-1, @fromdate)
set @sql = @sql + ' AND b.CTDate >= ''' + convert(char(13), @fromdate,112) + ''''
end
IF @todate IS NOT NULL
begin
set @todate= DATEADD(year,-1, @todate)
set @sql = @sql + ' and b.CTDate <= ''' + convert(char(13), @todate,112) + ''''
end
set @sql='Insert into #tempPreviousWinTrainingLog (EmplNO,QuizName, CTDate)'+ @sql
exec (@sql)


set @sql='select * from #tempPreviousWinTrainingLog '
set @sql ='select distinct o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbcchEmp o, tblPreviousWinTrainingLog L
where exists (select *
from dbo.#tempPreviousWinTrainingLog L
where L.Emplno= o.EMPLNO ) '


set @sql =@sql+ 'and not exists (select 1 from tblCurrentWinTrainingLog c
where o.EMPLNO=c.EMPLNO '

IF @quiz IS NOT NULL
begin
set @sql = @sql + ' AND c.QuizName =''' + @quiz +''''
end
set @sql = @sql +')'


IF @cc IS NOT NULL
begin
set @sql = @sql+' and o.JObCCNO in('+ @cc +')'
end
IF @unit IS NOT NULL
if len(ltrim(@unit))<>0
begin
set @sql = @sql + ' AND o.Unit = '''+ ltrim(rtrim(@unit)) +''''
end
end
IF @Debug = 1

BEGIN

PRINT @sql PRINT ''

END


exec (@sql)


GO

Thx. It time out over 300 set up in my web application.

 

[yan19454]

btw, my EMPLNO is nvarchar(50).