Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Incorrect syntax near '.' in sp

Status
Not open for further replies.
yan19454

yan19454

MIS
May 19, 2005
94
US
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, '')



DECLARE @OcchEmpSelect TABLE

(
EMPLNO nvarchar(6),
Unit nvarchar(2),
JobCCNO nvarchar(9)

)

insert into @OcchEmpSelect(EMPLNO, Unit, JobCCNO) SELECT EMPLNO , Unit , JobCCNo
FROM OcchEmp o
WHERE (@Unit is NUll or Unit = @Unit) AND (@cc is null or JobCCNo = @cc) and not exists (select 1 from tblCurrentWinTrainingLog c where o.EmplNO=c.Emplno and (@quiz is null or QuizName=@quiz))


SELECT DISTINCT @OcchEmpSelect.EMPLNO as EmplNO, tblPreviousWinTrainingLog.QuizName as QuizName, MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate
FROM @OcchEmpSelect INNER JOIN
tblPreviousWinTrainingLog ON OcchEmp.EMPLNO = tblPreviousWinTrainingLog.EmplNO
GROUP BY OcchEmp.EMPLNO, tblPreviousWinTrainingLog.QuizName, tblPreviousWinTrainingLog.CTDate
HAVING (
tblPreviousWinTrainingLog.QuizName = (@quiz is null or QuizName=@quiz) AND
( (@fromdate is null) or ( MAX(tblPreviousWinTrainingLog.CTDate) >@fromdate) or
(@fromdate is null) or ( MAX(tblPreviousWinTrainingLog.CTDate) < @todate)
)

end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Server: Msg 170, Level 15, State 1, Procedure DueListWithPreviousTraining_Temp, Line 34
Line 34: Incorrect syntax near '.'.

I could not figure out why error. Thx.
 
Sort by date Sort by votes
Could it be coming from here:
Code: 
insert into @OcchEmpSelect(EMPLNO, Unit, JobCCNO)  SELECT     EMPLNO , Unit , JobCCNo 
FROM         OcchEmp o
WHERE     (@Unit is NUll or Unit = @Unit) AND (@cc is null or JobCCNo = @cc) and not exists (select 1 from tblCurrentWinTrainingLog c where o.EmplNO=[COLOR=red]c.Emplno[/color] and (@quiz is null or QuizName=@quiz))
Where table is "c"?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
When using a table variable in a select like this, you must give it an alias.

Code: 
SELECT DISTINCT  [!]OcchEmp[/!].EMPLNO as EmplNO, tblPreviousWinTrainingLog.QuizName as QuizName, MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate
FROM          @OcchEmpSelect [!] As OcchEmp[/!] INNER JOIN
                      tblPreviousWinTrainingLog ON OcchEmp.EMPLNO = tblPreviousWinTrainingLog.EmplNO
GROUP BY OcchEmp.EMPLNO, tblPreviousWinTrainingLog.QuizName, tblPreviousWinTrainingLog.CTDate
HAVING   (
           tblPreviousWinTrainingLog.QuizName = (@quiz is null or QuizName=@quiz) AND
         (  (@fromdate is null) or (  MAX(tblPreviousWinTrainingLog.CTDate) >@fromdate) or 
            (@fromdate is null) or (  MAX(tblPreviousWinTrainingLog.CTDate) < @todate)
          )



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Never mind, it's too early in the morning, apparently... I see the table being referenced... Besides, it looks like George caught it... [thumbsup]

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
SELECT DISTINCT a.EMPLNO as EmplNO, tblPreviousWinTrainingLog.QuizName as QuizName, MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate
FROM @OcchEmpSelect as a INNER JOIN
tblPreviousWinTrainingLog ON a.EMPLNO = tblPreviousWinTrainingLog.EmplNO
GROUP BY a.EMPLNO, tblPreviousWinTrainingLog.QuizName, tblPreviousWinTrainingLog.CTDate
HAVING (
tblPreviousWinTrainingLog.QuizName = (@quiz is null or a.QuizName=@quiz) AND
( (@fromdate is null) or ( MAX(tblPreviousWinTrainingLog.CTDate) >@fromdate) or
(@fromdate is null) or ( MAX(tblPreviousWinTrainingLog.CTDate) < @todate)
)


I do not know whether I can use (@fromdate is null) and CTdate=@fromdate in having

Server: Msg 156, Level 15, State 1, Procedure DueListWithPreviousTraining_Temp, Line 39
Incorrect syntax near the keyword 'is'.
 
Upvote 0 Downvote
Not sure off the top of my head, but you appear to have an extra open parenthesis without a closing parenthesis in your HAVING statement. That will cause an issue...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
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, '')



DECLARE @OcchEmpSelect TABLE

(
EMPLNO nvarchar(6),
Unit nvarchar(2),
JobCCNO nvarchar(9)

)

insert into @OcchEmpSelect(EMPLNO, Unit, JobCCNO) SELECT EMPLNO , Unit , JobCCNo
FROM OcchEmp o
WHERE (@Unit is NUll or Unit = @Unit) AND (@cc is null or JobCCNo = @cc) and not exists (select 1 from tblCurrentWinTrainingLog c where o.EmplNO=c.Emplno and (@quiz is null or QuizName=@quiz))


SELECT DISTINCT a.EMPLNO as EmplNO, tblPreviousWinTrainingLog.QuizName as QuizName, MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate
FROM @OcchEmpSelect as a INNER JOIN
tblPreviousWinTrainingLog ON a.EMPLNO = tblPreviousWinTrainingLog.EmplNO
GROUP BY a.EMPLNO, tblPreviousWinTrainingLog.QuizName, tblPreviousWinTrainingLog.CTDate
HAVING (
tblPreviousWinTrainingLog.QuizName = (@quiz is null or a.QuizName=@quiz) AND
( (@fromdate is null) or ( MAX(tblPreviousWinTrainingLog.CTDate) >@fromdate) and
(@todate is null) or ( MAX(tblPreviousWinTrainingLog.CTDate) < @todate)
)

end
I do not think so. since I line up and double check it alreay.


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 
Upvote 0 Downvote
Your problem is here...

tblPreviousWinTrainingLog.QuizName = (@quiz is null or a.QuizName=@quiz)


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
I tried that
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, '')



DECLARE @OcchEmpSelect TABLE

(
EMPLNO nvarchar(6),
Unit nvarchar(2),
JobCCNO nvarchar(9)

)

insert into @OcchEmpSelect(EMPLNO, Unit, JobCCNO) SELECT EMPLNO , Unit , JobCCNo
FROM OcchEmp o
WHERE (@Unit is NUll or Unit = @Unit) AND (@cc is null or JobCCNo = @cc) and not exists (select 1 from tblCurrentWinTrainingLog c where o.EmplNO=c.Emplno and (@quiz is null or QuizName=@quiz))


SELECT DISTINCT a.EMPLNO as EmplNO, tblPreviousWinTrainingLog.QuizName as QuizName, MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate
FROM @OcchEmpSelect as a INNER JOIN
tblPreviousWinTrainingLog b ON a.EMPLNO = b.EmplNO
GROUP BY a.EMPLNO, b.QuizName, b.CTDate
HAVING (
b.QuizName = (@quiz is null or b.QuizName=@quiz)
)

end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I also tried that.

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, '')



DECLARE @OcchEmpSelect TABLE

(
EMPLNO nvarchar(6),
Unit nvarchar(2),
JobCCNO nvarchar(9)

)

insert into @OcchEmpSelect(EMPLNO, Unit, JobCCNO) SELECT EMPLNO , Unit , JobCCNo
FROM OcchEmp o
WHERE (@Unit is NUll or Unit = @Unit) AND (@cc is null or JobCCNo = @cc) and not exists (select 1 from tblCurrentWinTrainingLog c where o.EmplNO=c.Emplno and (@quiz is null or QuizName=@quiz))


SELECT DISTINCT a.EMPLNO as EmplNO, tblPreviousWinTrainingLog.QuizName as QuizName, MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate
FROM @OcchEmpSelect as a INNER JOIN
tblPreviousWinTrainingLog ON a.EMPLNO = b.EmplNO
GROUP BY a.EMPLNO, tblPreviousWinTrainingLog.QuizName, b.CTDate
HAVING (
tblPreviousWinTrainingLog.QuizName = (@quiz is null or tblPreviousWinTrainingLog.QuizName=@quiz)
)

end


GO
still the same error. I suspect optional parameter did not work this way in having condition.


Server: Msg 156, Level 15, State 1, Procedure DueListWithPreviousTraining_Temp, Line 39
Incorrect syntax near the keyword 'is'.
 
Upvote 0 Downvote
Try something like this...

(@quiz is null or tblPreviousWinTrainingLog.QuizName = @Quiz) and (@quiz is null or tblPreviousWinTrainingLog.QuizName=@quiz)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
803
SashaBuilder3
SashaBuilder3
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
727
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
583
SaltyTheFrog
SaltyTheFrog
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
461
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top