what is wrong with parameter stored procedure.
-
I fixed with single quote. thanks.
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 @mName ='PartI'
begin
@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
go
-
You are missing parentheses around the parameters:
CREATE PROCEDURE [dbo].[get_phy_list] (
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
)
AS
-
Thanks.
I have one running stored procedure. I did not put the () there ,too.
The errro is this line. I think it must be single code for @moduleName Thanks.
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
CREATE PROCEDURE [dbo].[get_emp_list_FY05]
@DateFrom datetime,
@DateTo_in datetime,
@strDept varchar(12)
AS
declare @DateTo datetime
select @DateTo = @DateTo_in+1
SELECT Annual_Edu_2006.dbo.HREMP.[FULL NAME], Annual_Edu_2006.dbo.HREMP.DOB, Annual_Edu_2006.dbo.HREMP.CC, Annual_Edu_2006.dbo.HREMP.CCNAME
FROM Annual_Edu_2006.dbo.HREMP INNER JOIN
(SELECT DISTINCT [Employee Name], cast(DOB AS [smalldatetime]) AS datebirth
FROM dbo.[Module Log FY05]
Where (dbo.[Module Log FY05].[Access Module Time] >= @DateFrom and dbo.[Module Log FY05].[Access Module Time] <= @DateTo)
UNION
SELECT DISTINCT [Employee Name], cast(DOB AS [smalldatetime]) AS datebirth
FROM dbo.[Quiz Log FY05]
Where (dbo.[Quiz Log FY05].[Access Quiz Time] >= @DateFrom and dbo.[Quiz Log FY05].[Access Quiz Time] <= @DateTo) ) p
ON Annual_Edu_2006.dbo.HREMP.[FULL NAME] = p.[Employee Name] AND Annual_Edu_2006.dbo.HREMP.DOB = p.datebirth
WHERE ltrim(rtrim( Annual_Edu_2006.dbo.HREMP.cc)) = @strDept and (( Annual_Edu_2006.dbo.HREMP.EMPSTATUS IS NULL) OR
( Annual_Edu_2006.dbo.HREMP.EMPSTATUS <> 90) )
GO
-
Your "fix with a single quote" is not a fix and introduces another bug.
The root problem is this line:
@moduleName ='Part I'
It should be
SET @moduleName ='Part I'
-
RIGHTNOW IT IS END HAS ERROR. Thanks..
Server: Msg 156, Level 15, State 1, Procedure get_phy_list, Line 30
Incorrect syntax near the keyword 'END'.
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 @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
else
if @mName ='PartII'
set @moduleName='Part II'
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)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ')
end
else
if @mName ='PartIII'
set @moduleName='Part III'
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)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
if @mName ='Magic'
set @moduleName ='Magic Web'
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)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
else
if @mName ='Infusion'
set @moduleName='New - Infus Instr/RX Dir'
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)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
if @mName ='New'
set @moduleName='New - Trans Pt Funct'
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)
and (dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' )
end
else
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
-
eg:
select a.* from authors a inner join (select * from titleauthor) as b
on a.au_id=b.au_id
where ..................
-Krishnan
-
I have taken the if clause out of the query. There is the Inner Join clause, but you don't specify the columns on which to join TrainingName and the subquery. So, the inner join is incomplete, and the parser doesn't like the "END" keyword
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply