Make following changes in your procedure it will definitely work for you.
CREATE PROCEDURE searchData
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
DECLARE @DemoDate DATETIME
SET @DemoDate = GETDATE()
SELECT * FROM Employee
WHERE COALESCE(JoinDate, @DemoDate)
BETWEEN COALESCE(@StartDate, JoinDate, @DemoDate)
AND COALESCE(@EndDate, JoinDate, @DemoDate)
END
Whether parameters @StartDate, @EndDate having null values or date values your procedure will work fine. If you are dam sure that JoinDate in table is always not null then following changes are sufficient.
CREATE PROCEDURE searchData
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
SELECT * FROM Employee
WHERE JoinDate
BETWEEN COALESCE(@StartDate, JoinDate)
AND COALESCE(@EndDate, JoinDate)
END
Any suggestions are acceptable.