CREATE TABLE [dbo].[PerformancePercentage] (
[PerformanceID] INT IDENTITY (1, 1) NOT NULL,
[EmployeeCategoryID] INT NOT NULL,
[BusinessCategoryID] INT NOT NULL,
[MonthlyOutputValueMin] DECIMAL (4, 2) NULL,
[MonthlyOutputValueMax] DECIMAL (4, 2) NULL,
[AssignedCountMin] INT NULL,
[AssignedCountMax] INT NULL,
[IsAssgined] BIT NULL,
[Percentage] INT NOT NULL)
上面表中MonthlyOutputValueMin、MonthlyOutputValueMax和AssignedCountMin、AssignedCountMax实际上表示一个区间,用来确定Percentage的。
写了个存储过程,想根据参数得到Percentage
create proc GetPerformancePercent
@EmployeeCategoryID int,@BusinessCategoryID int,
@MonthlyOutputValue decimal(4,2),@AssignedCount int,@IsAssgined bit,
@Percentage int output
as
select @Percentage=[Percentage] from [PerformancePercentage]
where [EmployeeCategoryID]=@EmployeeCategoryID and [BusinessCategoryID]=@BusinessCategoryID
and (case when [MonthlyOutputValueMin] is not null then [MonthlyOutputValueMin] end)<@MonthlyOutputValue
and (case when [MonthlyOutputValueMax] is not null then [MonthlyOutputValueMax] end)>@MonthlyOutputValue
and (case when [AssignedCountMin] is not null then [AssignedCountMin] end)<@AssignedCount
and (case when [AssignedCountMax] is not null then [AssignedCountMax] end)>@AssignedCount
and (case when [IsAssgined] is not null then [IsAssgined] end)=@IsAssgined
go
上面@MonthlyOutputValue,@AssignedCount和@IsAssgined可能为null
我的想法是比如当@AssignedCount为null的时候,表中对应的行对应的列中AssignedCount也为null,我想在where句中的case when中就舍去这个限制条件,
如果没有@AssignedCount不是null的话,表中对应的行也没有null,此时就按照大于或者等于的关系匹配。
实际操作的时候当存在null的情况总是得不到@Percentage
------解决方案--------------------
还可以考虑用或逻辑来回避NULL
类似于:
and ([MonthlyOutputValueMin] is null or @MonthlyOutputValue is null or [MonthlyOutputValueMin]<@MonthlyOutputValue)