CODEWITHSUNDEEP
sqlsql-serverstored-proceduresget
neoslo
neoslo

Reputation: 423

How to get around date parameter in SQL Server stored procedure to get back all data

I am currently using a getShifts stored procedure to get data for 2 separate features on the front end. My stored procedure requires a parameter Selected Date which when passed a date returns records for the specific date.

I am trying to bypass the ShiftDate parameter for one of my features and have it act as a get all. Is there a way to pass a fake date to the stored procedure in order to receive all records? 

BEGIN
    SELECT 
        n.ID, n.ID, m.SearchFullNameFL as ProfileName, n.SchedID, 
        s.LicenseID, f.ID as UnitID, f.UnitName, 
        n.Hours, s.ShiftDate, s.SchedShiftID, ss.Description as SchedShift,  
        ss.Hours as SchedShiftHours, t.ID as TransactionTypeId,
        (SELECT COUNT(b.ID) 
         FROM Sched b
         INNER JOIN Ref_SchedShift ss ON ss.ID = b.SchedShiftID AND ss.DSN > 0
         WHERE b.ExpiredBy = 0 AND b.ShiftDate = @ShiftDate -->= @StartDate 
           AND b.EndDate <= @EndDate
           AND b.FacUnitID IN (SELECT Id FROM FacUnit 
                               WHERE FacilityID = @FacilityID) 
           AND b.ID = s.ID) AS ShiftsWorked,
        n.OverTimeReasonTypeID, n.Comment, n.Ref_NoLunchID as NoLunchID, 
        nl.NoLunchType
    FROM
        NewClockIns n
    INNER JOIN 
        Members m ON m.ID = n.ID
    INNER JOIN 
        Sched s ON s.ID = n.SchedID AND s.ExpiredBy = 0
    INNER JOIN 
        Ref_Method rm ON n.Ref_MethodID = rm.ID
    INNER JOIN 
        Ref_ClockTransactionType t ON t.ID = n.Ref_ClockTransID
    INNER JOIN 
        FacUnit f ON f.ID = n.FacUnitID
    INNER JOIN 
        Ref_License l ON l.ID = s.LicenseID
    INNER JOIN 
        Ref_SchedShift ss ON ss.Id = s.SchedShiftID
    LEFT JOIN 
        Ref_NoLunch nl ON nl.ID = n.Ref_NoLunchID
    WHERE  
        s.ShiftDate = @ShiftDate-->= @StartDate 
        AND s.EndDate <= @EndDate 
        AND s.ClockInsAgencyApprovedByID > 0 
        AND ((@IsApproved = 1 AND s.ClockInsFacilityApprovedByID > 0) 
             OR 
             (@IsApproved = 0 AND s.ClockInsFacilityApprovedByID = 0)) 
        AND f.Id IN (SELECT ID FROM @UnitIDList) 
        AND n.ExpiredBy = 0
END

Upvotes: 1

Views: 163

Answers (2)

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

Try this:- 

ShiftDate =(Case When @IsApproved = 1 Then @ShiftDate Else null End)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Are you looking for or?

(s.ShiftDate = @ShiftDate or @ShiftDate is null)

Then NULL can be passed in and the filter is ignored.

Upvotes: 1

Related Questions