sql server-sql存储过程中的按日期可选参数搜索

Question

我想在搜索时从休假申请表中提取休假报告。在表中，我有Leavefrom（datetime）、LeaveTo（datetime（日期时间））列。现在我想在这两列的基础上取行。我的搜索参数可以为null，它们是@employeeid，@datefrom，@dateto。

我需要得到的结果必须介于Leavefrom和LeaveTo之间。我正在尝试为此创建一个存储过程。

ALTER PROCEDURE [dbo].[SP_GetSpecificLeaveReport]
@empid int=null,
@leavefrom date=null,
@leaveto date=null
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ela.appliedDate,ela.appliedBy,ela.leaveFrom,ela.leaveTo,ela.noOfDays,
    ejd.firstName,ejd.lastName,
    ltm.leaveType
    from dbo.tblEmployeeLeaveApplication as ela inner join dbo.tblEmployeeJobDetails as
    ejd on ela.empId=ejd.recordId inner join dbo.tblLeaveTypeMaster as ltm 
    on ela.leaveTypeId=ltm.record_Id where

END

Answer 1

这类查询被称为catch-all查询
有多种方法可以做到这一点，像Mukesh的回答中那样使用iif就是其中之一，但只能在sql server 2012或更高版本上使用。我建议使用稍长的where子句，以获得更好的性能和兼容性（这应该适用于任何版本，甚至是sql server 7）：

where (@empid is null or ela.empId = @empid)
and (@leavefrom is null or (
                                 ltm.leavedatefrom >= @leavefrom
                             and ltm.leavedatefrom < dateadd(day, 1, @leavefrom)
                           )
and (@leaveto is null or (
                                 ltm.leavedateto >= @leaveto
                             and ltm.leavedateto < dateadd(day, 1, @leaveto))

注意：由于数据库列的类型为datetime，但参数的类型为date，因此我有>=...or条件来捕获与特定日期匹配的所有日期时间值
此外，您可能需要从date强制转换为datetime。

此外，您应该意识到，使用catch-all查询可能会由于查询计划兑现而导致性能不佳
如果确实遇到性能问题，您可能希望在查询中添加重新编译提示，以便每次执行存储过程时都能获得最佳的查询计划。阅读本文了解更多详细信息。

Answer 2

尝试此查询以添加

where ela.empId=IFNULL(ela.empId,ela.empId)
and ltm.leavedatefrom>=IFNULL(@leavefrom,ltm.leavedatefrom)
and ltm.leavedateto<=IFNULL(@leaveto,ltm.leavedateto)

Answer 3

尝试以下操作，

ALTER PROCEDURE [dbo].[SP_GetSpecificLeaveReport]
        @empid int=null,
        @leavefrom date=null,
        @leaveto date=null
    AS
    BEGIN
        SET NOCOUNT ON;
            SELECT 
                ela.appliedDate,ela.appliedBy,ela.leaveFrom,ela.leaveTo,ela.noOfDays,
                ejd.firstName,ejd.lastName,
                ltm.leaveType
            from dbo.tblEmployeeLeaveApplication as ela 
                inner join dbo.tblEmployeeJobDetails as ejd on ela.empId=ejd.recordId 
                inner join dbo.tblLeaveTypeMaster as ltm on ela.leaveTypeId=ltm.record_Id 
            where
            1 = case when Isnull(@empid,0) == 0 then 
                    case when ela.empId == @empid then 1 else 0 end
                else 0 end
            And case when isnull(@leavefrom,'1900-01-01') == '1900-01-01' then 
                    case when ela.leaveFrom >= @leavefrom then 1 else 0 end
                else 0 end
            And case when isnull(@leavefrom,'1900-01-01') == '1900-01-01' then 
                    case when ela.leaveto <= @leaveto then 1 else 0 end
                else 0 end

    END