Stored Procedure with Optional DateTime Parameters in SQL Server

Question

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

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

Sam · Answer 1 · 2013-11-10T18:16:52.373

13

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

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

edited Nov 10 '13 at 18:16

answered Nov 10 '13 at 14:20

Sam

7,245
3
25
37

I was trying to use GETDATE() as an option default..and kept running into errors..this fixed that. – Chris Catignani Nov 13 '19 at 19:39

score 8 · Answer 2 · edited May 23 '17 at 10:31

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))

score 5 · Accepted Answer · edited Aug 09 '18 at 15:18

5

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.

edited Aug 09 '18 at 15:18

EstevaoLuis

2,422
7
33
40

answered Aug 09 '18 at 14:20

Shah

66
1
1

Rahul Tripathi · Answer 4 · 2013-11-10T14:25:32.437

0

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

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

edited Nov 10 '13 at 14:25

answered Nov 10 '13 at 14:18

Rahul Tripathi

168,305
31
280
331

But I need to know how to handle datetime in select query in SP – user1030181 Nov 10 '13 at 14:23
@user1030181:- Updated my answer. Hope that helps now? – Rahul Tripathi Nov 10 '13 at 14:25

score 0 · Answer 5 · answered Oct 14 '15 at 06:13

0

For null passing to date Field

declare dob string

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

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

inside procedure @dob date=NULL,

answered Oct 14 '15 at 06:13

Arun Prasad E S

9,489
8
74
87

Stored Procedure with Optional DateTime Parameters in SQL Server

5 Answers5