Is there a better way of writing this i.e. dynamically generating the WHERE clause within the procedure depending on the parameters values passed in? Thanks for your help

"CREATE PROCEDURE dbo.ShiftsBySurgery (@surgeryID as int) AS

    if (@surgeryID > 0)
        SELECT dbo.Area.area, dbo.Rota.rotaName, RTRIM(dbo.Users.forename) + ' ' + RTRIM(dbo.Users.surname) AS doctorName,
                         dbo.DoctorShift.shiftDate, '£' + CAST(dbo.DoctorShift.hourlyRate AS VARCHAR) AS hourlyRate, '£' + CAST(dbo.DoctorShift.adjustment AS VARCHAR)
                              AS adjustment, dbo.DoctorShift.adjustmentComments, dbo.fn_LZ(2, dbo.DoctorShift.startShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.startShiftMins)
                              AS startTime, dbo.fn_LZ(2, dbo.DoctorShift.endShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.endShiftMins) AS endTime, dbo.Surgery.name
            FROM dbo.Surgery INNER JOIN
                          dbo.Doctor ON dbo.Surgery.surgeryID = dbo.Doctor.surgeryID INNER JOIN
                              dbo.Users INNER JOIN
                              dbo.DoctorShift ON dbo.Users.userID = dbo.DoctorShift.gmcCode ON dbo.Doctor.gmcCode = dbo.Users.userID INNER JOIN
                              dbo.Area INNER JOIN
                              dbo.Rota ON dbo.Area.trustID = dbo.Rota.trustID AND dbo.Area.areaID = dbo.Rota.areaID ON dbo.DoctorShift.trustID = dbo.Rota.trustID AND
                              dbo.DoctorShift.areaID = dbo.Rota.areaID AND dbo.DoctorShift.rotaID = dbo.Rota.rotaID
            WHERE dbo.DoctorShift.cancellationID IS NULL AND dbo.Surgery.surgeryID = @surgeryID
            ORDER BY dbo.DoctorShift.shiftDate, starttime, endtime
    else
        SELECT dbo.Area.area, dbo.Rota.rotaName, RTRIM(dbo.Users.forename) + ' ' + RTRIM(dbo.Users.surname) AS doctorName,
                         dbo.DoctorShift.shiftDate, '£' + CAST(dbo.DoctorShift.hourlyRate AS VARCHAR) AS hourlyRate, '£' + CAST(dbo.DoctorShift.adjustment AS VARCHAR)
                              AS adjustment, dbo.DoctorShift.adjustmentComments, dbo.fn_LZ(2, dbo.DoctorShift.startShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.startShiftMins)
                              AS startTime, dbo.fn_LZ(2, dbo.DoctorShift.endShiftHrs) + ':' + dbo.fn_LZ(2, dbo.DoctorShift.endShiftMins) AS endTime, dbo.Surgery.name
            FROM dbo.Surgery INNER JOIN
                          dbo.Doctor ON dbo.Surgery.surgeryID = dbo.Doctor.surgeryID INNER JOIN
                              dbo.Users INNER JOIN
                              dbo.DoctorShift ON dbo.Users.userID = dbo.DoctorShift.gmcCode ON dbo.Doctor.gmcCode = dbo.Users.userID INNER JOIN
                              dbo.Area INNER JOIN
                              dbo.Rota ON dbo.Area.trustID = dbo.Rota.trustID AND dbo.Area.areaID = dbo.Rota.areaID ON dbo.DoctorShift.trustID = dbo.Rota.trustID AND
                              dbo.DoctorShift.areaID = dbo.Rota.areaID AND dbo.DoctorShift.rotaID = dbo.Rota.rotaID
            WHERE dbo.DoctorShift.cancellationID IS NULL
            ORDER BY dbo.DoctorShift.shiftDate, starttime, endtime
GO

It looks like you pass in 0 when you want all surgeries - if you can change this to pass in NULL in that case then you can do away with the if ... else ... and just change your WHERE clause to:

WHERE dbo.DoctorShift.cancellationID IS NULL AND dbo.Surgery.surgeryID = COALESCE(@surgeryID, dbo.Surgery.surgeryID)

The COALESCE function returns the first non-NULL argument, so if @surgeryID is NULL then you have
WHERE ... AND dbo.Surgery.surgeryID = dbo.Surgery.surgeryID
so you get all surgeries (i.e. @surgeryID is ignored)

but if @surgeryID is non-NULL you have
WHERE ... AND dbo.Surgery.surgeryID = @surgeryID
so you only get the surgeries you want

hth
Phil

Thanks thats a lot neater than the original and should be more useful when using multiple filters passed in as parameters.

Cheers

Doug.