代码之家 › 专栏 › 技术社区 › DeanKlos

将CASE WHEN表达式用于标量函数-T-SQL

sql-server-2005 tsql sql-server

DeanKlos · 技术社区 · 11 年前

我在下面有一个标量值函数,但想添加一个CASE WHEN表达式,但到目前为止还未能获得正确的语法。

USE [MYDatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[leaveBalanceByEmployeeID] (@EmployeeID INT)
RETURNS decimal(10,1)
AS
BEGIN
  DECLARE @returnValue decimal(10,1)
 SELECT @returnValue = MIN(dbo.LeaveMaster.LeaveDaysCarriedFoward + dbo.MonthsInService(dbo.LeaveMaster.DaysCarriedFowardCutoffDate, GETDATE()) 
            * dbo.EmployeeTypes.MonthlyLeaveAssignment)  - (SELECT     ISNULL(SUM(ActualLeaveDaysTaken), 0) AS LV
           FROM  dbo.LeaveApplications
           WHERE (EmployeeRecordID = dbo.EmployeeMaster.id AND HRMApproval = 'True' AND MarkAsDeleted = 'False'))  - CASE WHEN 0 <
           (SELECT ISNULL(SUM(DaysAuthorised),0) AS ECL
           FROM  dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_2
           WHERE (MasterEmployeeID = dbo.EmployeeMaster.id AND YEAR(CompassionateLeaveApplications_2.AuthorisedFromDate) =YEAR(GETDate())))  - ISNULL(MAX(dbo.Districts.CompassionateDays),0) THEN
           (SELECT  ISNULL(SUM(DaysAuthorised),0) AS ECL
           FROM   dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_2
           WHERE  (MasterEmployeeID = dbo.EmployeeMaster.id) AND YEAR(CompassionateLeaveApplications_2.AuthorisedFromDate) =YEAR(GETDate())) - ISNULL(MAX(dbo.Districts.CompassionateDays),0) ELSE 0 END                                                     
FROM       dbo.EmployeeMaster Full outer join dbo.LeaveMaster ON dbo.EmployeeMaster.id = dbo.LeaveMaster.EmpRecordID Full outer join
                      dbo.EmployeeTypes ON dbo.EmployeeMaster.EmployeeType = dbo.EmployeeTypes.ID Full outer join
                      Human_Resources.Departments ON dbo.EmployeeMaster.DepartmentId = Human_Resources.Departments.DepartmentID Full outer join
                      dbo.StaffHomeAddresses ON dbo.EmployeeMaster.id = dbo.StaffHomeAddresses.StaffID Full outer join
                      dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_1 ON 
                      dbo.EmployeeMaster.id = CompassionateLeaveApplications_1.MasterEmployeeID Full outer join
                      dbo.Districts ON dbo.StaffHomeAddresses.District = dbo.Districts.DistrictID
                      WHERE    dbo.EmployeeMaster.id = @EmployeeID
GROUP BY dbo.EmployeeMaster.id
return @returnValue
END

现在,表dbo.EmployeeMaster有一个名为IsActive的位列。我想要实现的是,如果IsActive column=True,那么@returnValue应该返回上面的查询将返回的内容,否则如果IsActive=False,那么我只想返回0.0

2 回复 | 直到 11 年前

Raj 11 年前

为什么不添加 IF-ELSE 相应地构造和查询,像这样?

USE [MYDatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[leaveBalanceByEmployeeID] (@EmployeeID INT)
RETURNS decimal(10,1)
AS
BEGIN
  DECLARE @returnValue decimal(10,1),@isActive bit;
  SELECT @isActive = IsActive FROM dbo.EmployeeMaster WHERE id = @EmployeeID;
  IF @isActive = 1
  BEGIN
  SELECT @returnValue = MIN(dbo.LeaveMaster.LeaveDaysCarriedFoward + dbo.MonthsInService(dbo.LeaveMaster.DaysCarriedFowardCutoffDate, GETDATE()) 
            * dbo.EmployeeTypes.MonthlyLeaveAssignment)  - (SELECT     ISNULL(SUM(ActualLeaveDaysTaken), 0) AS LV
           FROM  dbo.LeaveApplications
           WHERE (EmployeeRecordID = dbo.EmployeeMaster.id AND HRMApproval = 'True' AND MarkAsDeleted = 'False'))  - CASE WHEN 0 <
           (SELECT ISNULL(SUM(DaysAuthorised),0) AS ECL
           FROM  dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_2
           WHERE (MasterEmployeeID = dbo.EmployeeMaster.id AND YEAR(CompassionateLeaveApplications_2.AuthorisedFromDate) =YEAR(GETDate())))  - ISNULL(MAX(dbo.Districts.CompassionateDays),0) THEN
           (SELECT  ISNULL(SUM(DaysAuthorised),0) AS ECL
           FROM   dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_2
           WHERE  (MasterEmployeeID = dbo.EmployeeMaster.id) AND YEAR(CompassionateLeaveApplications_2.AuthorisedFromDate) =YEAR(GETDate())) - ISNULL(MAX(dbo.Districts.CompassionateDays),0) ELSE 0 END                                                     
  FROM       dbo.EmployeeMaster Full outer join dbo.LeaveMaster ON dbo.EmployeeMaster.id = dbo.LeaveMaster.EmpRecordID Full outer join
                      dbo.EmployeeTypes ON dbo.EmployeeMaster.EmployeeType = dbo.EmployeeTypes.ID Full outer join
                      Human_Resources.Departments ON dbo.EmployeeMaster.DepartmentId = Human_Resources.Departments.DepartmentID Full outer join
                      dbo.StaffHomeAddresses ON dbo.EmployeeMaster.id = dbo.StaffHomeAddresses.StaffID Full outer join
                      dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_1 ON 
                      dbo.EmployeeMaster.id = CompassionateLeaveApplications_1.MasterEmployeeID Full outer join
                      dbo.Districts ON dbo.StaffHomeAddresses.District = dbo.Districts.DistrictID
                      WHERE    dbo.EmployeeMaster.id = @EmployeeID
GROUP BY dbo.EmployeeMaster.id
END
ELSE 
BEGIN
SET @returnValue = 0;
END
return @returnValue
END

Santiago Regojo 11 年前

如果未激活,只需使用所需值初始化变量:

DECLARE @returnValue decimal(10,1) = 0.0

修改Where calusule以检查是否处于活动状态

 WHERE    dbo.EmployeeMaster.id = @EmployeeID and dbo.EmployeeMaster.IsActive = 1

通过添加该条件,变量将仅在活动时更新,在另一种情况下,它将包含初始化中使用的值。

USE [MYDatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[leaveBalanceByEmployeeID] (@EmployeeID INT)
RETURNS decimal(10,1)
AS
BEGIN
  DECLARE @returnValue decimal(10,1) = 0.0
 SELECT @returnValue = MIN(dbo.LeaveMaster.LeaveDaysCarriedFoward + dbo.MonthsInService(dbo.LeaveMaster.DaysCarriedFowardCutoffDate, GETDATE()) 
            * dbo.EmployeeTypes.MonthlyLeaveAssignment)  - (SELECT     ISNULL(SUM(ActualLeaveDaysTaken), 0) AS LV
           FROM  dbo.LeaveApplications
           WHERE (EmployeeRecordID = dbo.EmployeeMaster.id AND HRMApproval = 'True' AND MarkAsDeleted = 'False'))  - CASE WHEN 0 <
           (SELECT ISNULL(SUM(DaysAuthorised),0) AS ECL
           FROM  dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_2
           WHERE (MasterEmployeeID = dbo.EmployeeMaster.id AND YEAR(CompassionateLeaveApplications_2.AuthorisedFromDate) =YEAR(GETDate())))  - ISNULL(MAX(dbo.Districts.CompassionateDays),0) THEN
           (SELECT  ISNULL(SUM(DaysAuthorised),0) AS ECL
           FROM   dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_2
           WHERE  (MasterEmployeeID = dbo.EmployeeMaster.id) AND YEAR(CompassionateLeaveApplications_2.AuthorisedFromDate) =YEAR(GETDate())) - ISNULL(MAX(dbo.Districts.CompassionateDays),0) ELSE 0 END                                                     
FROM       dbo.EmployeeMaster Full outer join dbo.LeaveMaster ON dbo.EmployeeMaster.id = dbo.LeaveMaster.EmpRecordID Full outer join
                      dbo.EmployeeTypes ON dbo.EmployeeMaster.EmployeeType = dbo.EmployeeTypes.ID Full outer join
                      Human_Resources.Departments ON dbo.EmployeeMaster.DepartmentId = Human_Resources.Departments.DepartmentID Full outer join
                      dbo.StaffHomeAddresses ON dbo.EmployeeMaster.id = dbo.StaffHomeAddresses.StaffID Full outer join
                      dbo.CompassionateLeaveApplications AS CompassionateLeaveApplications_1 ON 
                      dbo.EmployeeMaster.id = CompassionateLeaveApplications_1.MasterEmployeeID Full outer join
                      dbo.Districts ON dbo.StaffHomeAddresses.District = dbo.Districts.DistrictID
                      WHERE    dbo.EmployeeMaster.id = @EmployeeID and dbo.EmployeeMaster.IsActive = 1 
GROUP BY dbo.EmployeeMaster.id
return @returnValue
END