I have often read a common question in forum posts of how to use ISNULL and Between Operators together with Date Functions in stored procedure. So here is the solution that i provided in this blog.
Create the following table to demonstrate the ISNULL and Between Operators together with Date Functions in stored procedure.
Next Create Stored Procedure using ISNULL and Between Opertaors together with Date Functions -
- Create Procedure USP_GetEmplyees
- @FROMDATE DATE = Null,
- @TODATE DATE = Null
- AS
- BEGIN
- SELECT EmpId, EMpName,Gender,CreatedOn,Status
- FROM Mas_Employee
- WHERE Status = 'A' AND
- Convert(varchar(10),CreatedOn,120)
- BETWEEN ISNULL(@FROMDATE,'1900-01-01') AND ISNULL(@TODATE,'9999-12-31')
- END
Test the Stored procedure by executing the following statements -
-
- EXEC USP_GetEmplyees
- EXEC USP_GetEmplyees Null
- EXEC USP_GetEmplyees Null, Null
-
-
- EXEC USP_GetEmplyees Null, '2015-03-28'
-
-
- EXEC USP_GetEmplyees '2015-03-28', Null
-
-
- EXEC USP_GetEmplyees '2015-03-28', '2015-03-28'
I hope you enjoyed it. Please provide the best ways by commenting to this blog.