how to write this sp
-
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.DueListWithPreviousTraining_Temp
@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
Set @quiz = NullIf(@quiz, '')
Set @Unit = NullIf(@Unit, '')
Select EmplNO, Max(CTdate) As MaxDate, QuizName
From tblPreviousWinTrainingLog b GROUP BY b.EmplNO, b.QuizName
HAVING (@fromdate is null or b.MAX(CTDate) > @fromdate ) AND ( @todate is null or b.MAX(CTDate) <@todate) AND
(@quiz or b.QuizName = @quiz)
end
Server: Msg 156, Level 15, State 1, Procedure DueListWithPreviousTraining_Temp, Line 27
Incorrect syntax near the keyword 'or'.
Thx.
-
Frances
It's in the last line - did you mean to say @quiz IS NULL or @quiz = ''?
John
-
Thx.
the stored compiled , but it gave error while I run it. I declared it in the first name.
Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@quiz'.
Stored Procedure: Annual_Edu_Test.dbo.DueListWithPreviousTraining_Temp
Return Code = 0
My other sp did not work well. the table scan is costly. I am going to rewrite that.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=342764
-
Yes. That one looks a bit scary. It should be possible to write it without dynamic SQL, though. And using joins instead of subqueries, where you can, will make it more readable and possibly also make it perform better.
John
-
but this is one scary, too. it gave me error.
-
rewrite the first part in . The begin is left out and
fixed the condition @quiz is null in the having clause
Set @quiz = NullIf(@quiz, '')
Set @Unit = NullIf(@Unit, '')
Select EmplNO, Max(CTdate) As MaxDate, QuizName
From tblPreviousWinTrainingLog b GROUP BY b.EmplNO, b.QuizName
HAVING (@fromdate is null or b.MAX(CTDate) > @fromdate )
AND ( @todate is null or b.MAX(CTDate) <@todate)
AND (@quiz is null or b.QuizName = @quiz)
-
ALTER PROCEDURE dbo.DueListWithPreviousTraining_Temp
@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
Set @quiz = NullIf(@quiz, '')
Set @Unit = NullIf(@Unit, '')
Select EmplNO, Max(CTdate) As MaxDate, QuizName
From tblPreviousWinTrainingLog b GROUP BY b.EmplNO, b.QuizName
HAVING (@fromdate is null or b.MAX(CTDate) > @fromdate ) AND ( @todate is null or b.MAX(CTDate) <@todate) AND
(@quiz is null or b.QuizName = @quiz)
end
Server: Msg 208, Level 16, State 1, Procedure DueListWithPreviousTraining_Temp, Line 29
Invalid object name 'b.MAX'.
Server: Msg 208, Level 16, State 1, Procedure DueListWithPreviousTraining_Temp, Line 29
Invalid object name 'b.MAX'.
Stored Procedure: Annual_Edu_Test.dbo.DueListWithPreviousTraining_Temp
-
Darn copy paste errors.
Try
Select EmplNO, Max(CTdate) As MaxDate, QuizName
From tblPreviousWinTrainingLog b
WHERE (@fromdate is null or CTDate > @fromdate )
AND ( @todate is null or CTDate <@todate)
AND (@quiz is null or b.QuizName = @quiz)
GROUP BY b.EmplNO, b.QuizName
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply