November 6, 2006 at 9:07 am
I have the following stored procedure. If I do not create temp table. It will have the 83 record out. ut if I use temp, it have no record return. Do not know why ? Thx.
ALTER PROCEDURE dbo.DueListWithPreviousTrainingTest
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@unit nchar(5) = NULL,
@cc nvarchar(2000) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)
begin
CREATE TABLE #tempPreviousWinTrainingLog (
[EmplNO] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CTDate] [datetime] NULL ,
[QuizName] [char] (25) COLLATE Latin1_General_CI_AS NULL
)
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 dbo.OcchEmp o, #tempPreviousWinTrainingLog 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
IF @cc IS NOT NULL
begin
set @sql = @sql+' and o.JObCCNO in('+ @cc +')'
end
IF @unit IS NOT NULL
set @sql = @sql + ' AND o.Unit = '''+ @unit +''''
IF @Debug = 1
BEGIN
PRINT @sql PRINT ''
END
end
November 7, 2006 at 8:36 am
First of all, I don't have enough information to validate your query. However, I would take an alternate approach and avoid using dynamic SQL. I assume that you are using dynamic SQL due to the IN clause associated with the @cc variable.
If you create a UDF that splits @cc and returns a table, with each value in its own row, then you can join to the UDF in your query.
I will assume that @cc is comma-delimited (i.e. 'ABC,DEF,GHI,...')
See http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1408
for a possible script. There are several version available on this site alone.
Then, you can change you stored procedure to something like this:
ALTER PROCEDURE dbo.DueListWithPreviousTrainingTest
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@unit nchar(5) = NULL,
@cc nvarchar(2000) = NULL,
@debug bit = 0
as
CREATE TABLE #tempPreviousWinTrainingLog (
[EmplNO] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CTDate] [datetime] NULL ,
[QuizName] [char] (25) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO #tempPreviousWinTrainingLog (EmplNO,QuizName, CTDate)
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)
AND b.QuizName = CASE
WHEN @quiz IS NOT NULL AND Len(@quiz)<>0 THEN @quiz
ELSE b.QuizName
END
AND b.CTDate >= CASE
-- style 112 returns 8 chars - why is this char(13)? char is padded with trailing spaces.
WHEN @fromdate IS NOT NULL THEN Convert(char(13), DATEADD(year, -1, @fromdate), 112)
ELSE b.CTDate
END
AND b.CTDate <= CASE
-- style 112 returns 8 chars - why is this char(13)? char is padded with trailing spaces.
WHEN @todate IS NOT NULL THEN Convert(char(13), DATEADD(year, -1, @todate), 112)
ELSE b.CTDate
END
SELECT DISTINCT o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
FROM dbo.OcchEmp o, #tempPreviousWinTrainingLog L
WHERE EXISTS (SELECT *
FROM dbo.#tempPreviousWinTrainingLog L
WHERE L.Emplno= o.EMPLNO
) -- EXISTS
AND NOT EXISTS (SELECT 1
FROM tblCurrentWinTrainingLog c
WHERE o.EMPLNO = c.EMPLNO
AND c.QuizName = CASE
WHEN @quiz IS NOT NULL THEN @quiz
ELSE c.QuizName
END --CASE
) -- AND NOT EXISTS
AND o.Unit = CASE
WHEN @unit IS NOT NULL THEN @unit
ELSE o.Unit
END
AND o.JObCCNO = CASE
WHEN @cc IS NULL
OR EXISTS (SELECT lt.ListItem
FROM dbo.f_reuParseList(@cc, ',') lt
WHERE lt.ListItem = o.JObCCNO)
THEN o.JObCCNO
ELSE NULL
END --CASE
November 8, 2006 at 7:07 pm
I think I need to define the dbo.f_reuParseList something like that here. Thanks.
dbo.f_reuParseList(@cc, ',') lt
WHERE lt.ListItem = o.JObCCNO)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply