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