lundi 29 juin 2015

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

Aucun commentaire:

Enregistrer un commentaire