sql-server - SQL Server 中带有可选日期时间参数的存储过程

Question

当 datetime 参数 (@StartDate & @EndDate) 时，如何在 SQL Server 中编写此 select 查询) 在此存储过程中是可选的吗？

CREATE PROCEDURE [dbo].[prSearchEmployees]
(
    @Id INT = NULL
    ,@FullName VARCHAR(20) = NULL
    ,@Age INT = NULL
    ,@StartDate = NULL
    ,@EndDate = NULL
)
AS
BEGIN
   SELECT * 
   FROM Employee 
   WHERE Id = ISNULL(@Id, Id) 
     AND FullName LIKE ISNULL(@FullName + '%', FullName)
     AND Age = ISNULL(@Age, Age)
END

Answer 1

您可以在存储过程的顶部添加两个新变量..并根据 ISNULL 函数进行分配

CREATE PROCEDURE [dbo].[prSearchEmployees]
(
 @Id INT = NULL
,@FullName VARCHAR(20) = NULL
,@Age INT = NULL
,@StartDate DATETIME = NULL
,@EndDate DATETIME = NULL
)
AS
BEGIN

DECLARE @SDate DATETIME
DECLARE @EDate DATETIME

SET @SDate = ISNULL(@StartDate, GETDATE())
SET @EDate = ISNULL(@EndDate, GETDATE())


SELECT * FROM Employee 
     WHERE Id = ISNULL(@Id, Id) 
       AND FullName LIKE ISNULL(@FullName + '%', FullName)
       AND Age = ISNULL(@Age, Age)
       AND Date BETWEEN @SDate AND @EDate