Help with SQL Statement

Discussion in 'MySQL' started by tomred, Jan 26, 2011.

  1. #1
    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
     
    tomred, Jan 26, 2011 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    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?
     
    jestep, Jan 26, 2011 IP
  3. tomred

    tomred Peon

    Messages:
    382
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #3
    @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
     
    tomred, Jan 28, 2011 IP