Hi, I have the following stored procedure, which is working fine: ( @BookingDate DATETIME, @BookingStart DATETIME, @BookingEnd DATETIME, @RoomOpening DATETIME, @RoomClosing DATETIME, @Attendees INT, @SiteID INT, @IsCatering varchar(50) ) AS SELECT Rooms.BuildingID, Rooms.RoomID, RoomName, MaxCapacity, MinCapacity, RoomOpening, RoomClosing, BuildingName, Photo1 FROM Rooms, Buildings, RoomPhotos WHERE Rooms.RoomID NOT IN ( SELECT DISTINCT RoomID FROM RoomBooking WHERE BookingDate = @BookingDate AND ( (BookingStart BETWEEN @BookingStart AND @BookingEnd) OR (BookingEnd BETWEEN @BookingStart AND @BookingEnd) OR (BookingStart <= @BookingStart AND BookingEnd >= @BookingEnd) ) ) AND RoomOpening <=@RoomOpening AND RoomClosing >= @RoomClosing AND (SELECT max(Capacity)as MaxCapacity FROM RoomLayouts where RoomLayouts.RoomID=Rooms.RoomID) >=@Attendees AND MinCapacity <=@Attendees AND Rooms.BuildingID = Buildings.BuildingID AND Rooms.RoomID = RoomPhotos.RoomID AND Buildings.SiteID = @SiteID AND Rooms.CateringAvailable = @IsCatering Code (markup): Where it says AND Rooms.CateringAvailable = @IsCatering I only want this to happen if the value of @IsCatering is True.... Something like if @IsCatering is 'True' then AND Rooms.CateringAvailable = True Else 'Just forget this statement End if
You define @IsCatering as a varchar(50). Do you need to make sure it is not null, or do you need the change the type of @IsCatering?
@IsCatering comes into the SP as a string with a value of True or False.....Thats fine I just need it to do 'AND Rooms.CateringAvailable = True' ONLY if the value @IsCatering = true