April 2, 2007 at 11:59 am
I tried to modify this sp
ALTER PROCEDURE [dbo].[get_SClinicals_list]
@DateFrom datetime='09/04/05',
@DateToIn datetime='03/04/06',
@mName varchar(50),
@ccName varchar(20)
AS
SET NOCOUNT ON
begin
declare @DateTo datetime
select @DateTo = @DateToIn+1
declare @moduleName varchar(50)
if @ccName='All' and @mName='All'
begin
SELECT distinct l.[Module Name] AS ModuleName, l.[Employee Name] AS [EmpName], CONVERT(CHAR(10),l.[Access Module Time],101) as TTime
FROM [Module Log] l INNER JOIN
[Module Name List SClinicals] c ON l.[Module Name] = c.[Module]
inner join [Annual_Edu_2006].dbo.HREMP h on l.[Employee Name] =h.[Full Name] and
l.[DOB] =h.[DOB] where l.[Access Module Time] >= @DateFrom and l.[Access Module Time] <= @DateTo
end
if (@ccName='All' and @mName<>'All' )
begin
SELECT distinct l.[Module Name] AS ModuleName, l.[Employee Name] AS [EmpName], CONVERT(CHAR(10),l.[Access Module Time],101) as TTime
FROM [Module Log] l INNER JOIN
[Module Name List SClinicals] c ON l.[Module Name] = c.[Module]
inner join [Annual_Edu_2006].dbo.HREMP h on l.[Employee Name] =h.[Full Name] and
l.[DOB] =h.[DOB] where
l.[Module Name] =@mName and l.[Access Module Time] >= @DateFrom and l.[Access Module Time] <= @DateTo
end
if ( @ccName<>'All' and @mName='All' )
begin
SELECT distinct l.[Module Name] AS ModuleName, l.[Employee Name] AS [EmpName], CONVERT(CHAR(10),l.[Access Module Time],101) as TTime
FROM [Module Log] l INNER JOIN
[Module Name List SClinicals] c ON l.[Module Name] = c.[Module]
inner join [Annual_Edu_2006].dbo.HREMP h on l.[Employee Name] =h.[Full Name] and
l.[DOB] =h.[DOB] where h.[CC]=@ccName
and l.[Access Module Time] >= @DateFrom and l.[Access Module Time] <= @DateTo
end
if ( @ccName<>'All' or @mName<>'All' )
begin
SELECT distinct l.[Module Name] AS ModuleName, l.[Employee Name] AS [EmpName], CONVERT(CHAR(10),l.[Access Module Time],101) as TTime
FROM [Module Log] l INNER JOIN
[Module Name List SClinicals] c ON l.[Module Name] = c.[Module]
inner join [Annual_Edu_2006].dbo.HREMP h on l.[Employee Name] =h.[Full Name] and
l.[DOB] =h.[DOB] where h.[CC]=@ccName and
l.[Module Name] =@mName and l.[Access Module Time] >= @DateFrom and l.[Access Module Time] <= @DateTo
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
end
I want when the parameter input @nName or @ccName as ALL, which not input it will not return all the records without restriction. If @DateFrom, or @DateTo have some value. it will return the selected dates. otherwise it will return all.
I think there will be a beter way to do that.
Thx.
April 3, 2007 at 12:32 pm
not sure if this is the simplified version you want.
ALTER PROCEDURE [dbo].[get_SClinicals_list]
@DateFrom datetime='18000101', /*lowest possible*/
@DateToIn datetime='99990101', /*highest possible*/
@mName varchar(50)=NULL,
@ccName varchar(20)=NULL
AS
SET NOCOUNT ON
IF @mName is null and @ccName IS NULL)
BEGIN
RAISERROR('At least one name required',16,1)
END
DECLARE @AllNames bit
SET @AllNames=0
IF @mName ='All' AND @ccName='All'
BEGIN
SET @AllNames=1
END
SELECT distinct l.[Module Name] AS ModuleName, l.[Employee Name] AS [EmpName], CONVERT(CHAR(10),l.[Access Module Time],101) as TTime
FROM [Module Log] l INNER JOIN
[Module Name List SClinicals] c ON l.[Module Name] = c.[Module]
inner join [Annual_Edu_2006].dbo.HREMP h on l.[Employee Name] =h.[Full Name] and
l.[DOB] =h.[DOB] where (h.[CC] =@ccName or @ccName='All' ) and
(l.[Module Name] =@mName or @mName='All') and l.[Access Module Time] >= @DateFrom and l.[Access Module Time] <= @DateTo
...
April 3, 2007 at 12:39 pm
DECLARE @AllNames bit
SET @AllNames=0
IF @mName ='All' AND @ccName='All'
BEGIN
SET @AllNames=1
END
and
where (h.[CC] =@ccName or @ccName='All' ) etc can simplify the code.
Thx.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply