CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Salam Indianoor
Salam Indianoor

Reputation: 105

Date wise optional parameter searching in SQL Stored Procedure

I want to take a leave report from my leave application table when I search. In the table I have Leavefrom(datetime), LeaveTo(datetime) columns. Now I want to take the rows on the basis of these two columns. My searching parameters are nullable they are @employeeid, @datefrom, @dateto.

I need to get the result must between the date of Leavefrom, LeaveTo. I am trying to make a stored procedure for this.

ALTER PROCEDURE [dbo].[SP_GetSpecificLeaveReport]
@empid int=null,
@leavefrom date=null,
@leaveto date=null
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ela.appliedDate,ela.appliedBy,ela.leaveFrom,ela.leaveTo,ela.noOfDays,
    ejd.firstName,ejd.lastName,
    ltm.leaveType
    from dbo.tblEmployeeLeaveApplication as ela inner join dbo.tblEmployeeJobDetails as
    ejd on ela.empId=ejd.recordId inner join dbo.tblLeaveTypeMaster as ltm 
    on ela.leaveTypeId=ltm.record_Id where



END

Upvotes: 1

Views: 487

Answers (3)

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

Try following,

ALTER PROCEDURE [dbo].[SP_GetSpecificLeaveReport]
        @empid int=null,
        @leavefrom date=null,
        @leaveto date=null
    AS
    BEGIN
        SET NOCOUNT ON;
            SELECT 
                ela.appliedDate,ela.appliedBy,ela.leaveFrom,ela.leaveTo,ela.noOfDays,
                ejd.firstName,ejd.lastName,
                ltm.leaveType
            from dbo.tblEmployeeLeaveApplication as ela 
                inner join dbo.tblEmployeeJobDetails as ejd on ela.empId=ejd.recordId 
                inner join dbo.tblLeaveTypeMaster as ltm on ela.leaveTypeId=ltm.record_Id 
            where
            1 = case when Isnull(@empid,0) == 0 then 
                    case when ela.empId == @empid then 1 else 0 end
                else 0 end
            And case when isnull(@leavefrom,'1900-01-01') == '1900-01-01' then 
                    case when ela.leaveFrom >= @leavefrom then 1 else 0 end
                else 0 end
            And case when isnull(@leavefrom,'1900-01-01') == '1900-01-01' then 
                    case when ela.leaveto <= @leaveto then 1 else 0 end
                else 0 end


    END

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

try this query to add

where ela.empId=IFNULL(ela.empId,ela.empId)
and ltm.leavedatefrom>=IFNULL(@leavefrom,ltm.leavedatefrom)
and ltm.leavedateto<=IFNULL(@leaveto,ltm.leavedateto)

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82544

This kind of queries are called catch-all queries.
There are multiple ways to do this, using iif like in Mukesh's answer is one of them, but will only work on sql server 2012 or higher. I would recommend working with a slighly longer where clause for better performance as well as compatibility (this should work with any version, even sql server 7):

where (@empid is null or ela.empId = @empid)
and (@leavefrom is null or (
                                 ltm.leavedatefrom >= @leavefrom
                             and ltm.leavedatefrom < dateadd(day, 1, @leavefrom)
                           )
and (@leaveto is null or (
                                 ltm.leavedateto >= @leaveto
                             and ltm.leavedateto < dateadd(day, 1, @leaveto))

Note: Since your database columns are of type datetime but your parameters are of type date, I've the >=...or condition to catch all datetime values that match a specific date.
Also, you might need to cast from date to datetime.

Also, you should be aware of the fact that using catch-all queries might suffer from poor performance due to query-plan cashing.
If you do encounter a performance problem you might want to add a recompile hint to your query so that each time you execute the stored procedure you will have the optimal query plan. read this article for more details.

Upvotes: 0

Related Questions