February 17, 2006 at 9:53 am
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 30
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 41
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 52
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 62
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 73
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 83
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 92
Incorrect syntax near the keyword 'end'.
Thanks.
CREATE PROCEDURE [dbo].[get_phy_list]
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
AS
declare @moduleName datetime
if @mName is null
begin
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
UNION
SELECT DISTINCT [License Number] as LicenseNumber
FROM dbo.[Doctor Quiz Log]
Where (dbo.[Doctor Quiz Log].[Access Quiz Time] >= @DateFrom and dbo.[Doctor Quiz Log].[Access Quiz Time] < @DateTo)) p
ON dbo.TrainingName.[LicenseNumber] = p.[LicenseNumber]
end
if @mName ='PartI'
BEGIN
set @moduleName ='Part I'
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ')
END
if @mName ='PartII'
begin
set @moduleName='Part II'
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ')
end
if @mName ='PartIII'
begin
set @moduleName='Part III'
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
if @mName ='Magic'
begin
set @moduleName ='Magic Web'
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
if @mName ='Infusion'
begin
set @moduleName='New - Infus Instr/RX Dir'
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
if @mName ='New'
begin
set @moduleName='New - Trans Pt Funct'
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
if @mName ='Quiz'
begin
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Quiz Log]
Where (dbo.[Doctor Quiz Log].[Access Quiz Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Quiz Time] < @DateTo)
and (dbo.[Doctor Quiz Log].[Quiz Name]= '+ @mName ' )
end
*/
GO
February 17, 2006 at 10:14 am
You are missing a BEGIN keyword:
CREATE PROCEDURE [dbo].[get_phy_list]
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
AS
BEGIN
February 17, 2006 at 10:17 am
I put a begin there before. It still have the error.
February 17, 2006 at 10:17 am
It also needs to be pointed out that many of your joins are incorrect syntax. You have multiple INNER JOINs that are missing their ON () join column list.
And of course the issue with variables inside single quotes, which isn't doing what you appear to think it is doing:
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName '
February 17, 2006 at 10:21 am
Why not start small ? get 1 subset working, then add additional IF conditions ?
CREATE PROCEDURE [dbo].[get_phy_list] (
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
)
AS
BEGIN
declare @moduleName datetime
if @mName is null
BEGIN
SELECT * from dbo.TrainingName
INNER JOIN (SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom
AND dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
UNION
SELECT DISTINCT [License Number] as LicenseNumber
FROM dbo.[Doctor Quiz Log]
WHERE (dbo.[Doctor Quiz Log].[Access Quiz Time] >= @DateFrom and dbo.[Doctor Quiz Log].[Access Quiz Time] < @DateTo)) p
ON dbo.TrainingName.[LicenseNumber] = p.[LicenseNumber]
END -- IF
END -- Procedure
February 17, 2006 at 10:27 am
The problem is not join. Once I took off the right. It worked. Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply