SQL Server中具有可选DateTime参数的存储过程

How to write this select query in SQL Server when the datetime parameters (@StartDate & @EndDate) are optional in this stored procedure?

当datetime参数(@StartDate和@EndDate)在此存储过程中是可选的时,如何在SQL Server中编写此选择查询?

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

5 个解决方案

#1

AND(
      ((NullIf(@begin, '') IS NULL)  OR CAST(ColumnDate AS DATE) >= @begin)  
    AND ((NullIf(@end, '') IS NULL)  OR CAST(ColumnDate AS DATE) <= @end)  
   )

covers all the options, nulls, blanks and comparisions.

涵盖所有选项,空值,空白和比较。

#2

You could add two new variables at the top of your sproc.. And assign based on the ISNULL function

您可以在sproc的顶部添加两个新变量。并根据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

#3

I know this is an old post but there are alternatives that are a bit more concise. This post outlines it perfectly. So in the previous example you would do something like

我知道这是一个老帖子,但有一些选择更简洁。这篇文章完美地概括了它。所以在前面的例子中你会做类似的事情

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

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

#4

You just have to define the datatype of @StartDate and @EndDate as Date. You may try like this:

您只需将@StartDate和@EndDate的数据类型定义为Date。你可以尝试这样:

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

SET @StDate = ISNULL(@StartDate ,NOW())
SET @EtDate = ISNULL(@EndDate , NOW())

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

#5

For null passing to date Field

对于null传递到日期字段

declare dob string

声明dob字符串

at object assignment stud_dtl_obj.dob = dob.Text == "" ? null : dob.Text;

在对象赋值stud_dtl_obj.dob = dob.Text ==“”? null:dob.Text;

at procedure assignment SqlHelper.MakeParam("@dob",obj_stud_dtl.dob),

在程序分配SqlHelper.MakeParam(“@ dob”,obj_stud_dtl.dob),

inside procedure @dob date=NULL,

内部程序@dob date = NULL,

#1

AND(
      ((NullIf(@begin, '') IS NULL)  OR CAST(ColumnDate AS DATE) >= @begin)  
    AND ((NullIf(@end, '') IS NULL)  OR CAST(ColumnDate AS DATE) <= @end)  
   )