1

I have following stored procedure. I expect the output with single record set which will have three columns (meeting_room_id, slot_id, min_booking_length) as parameters passed in stored procedure. Following stored procedure gives me record set from first select statement. How can I get intended output using this stored procedure? 

CREATE PROCEDURE validate_meeting_room_booking_slot(
    INOUT meeting_room_id BIGINT, 
    IN rent_space_start_time VARCHAR(255), 
    IN rent_space_end_time VARCHAR(255), 
    IN scheduled_date DATE, 
    OUT slot_id BIGINT, 
    OUT min_booking_length INT) 

    BEGIN 

      SELECT id INTO @slot_id
      FROM meeting_room_booked_slots 
      WHERE meeting_room_id = meeting_room_id 
        AND date = scheduled_date 
        AND ( 
             (start_time < rent_space_start_time 
                 AND end_time > rent_space_start_time) 
             OR 
             (start_time >rent_space_start_time 
                AND start_time < rent_space_start_time)) 
      LIMIT 1; 

      SELECT meeting_room_id, min_booking_length 
      FROM meeting_rooms
      WHERE id = meeting_room_id
        AND deleted_at IS NULL;

    END
Veshraj Joshi
  • 3,544
  • 3
  • 27
  • 45
  • Is the record correct, but selecting the wrong columns? – Lajos Arpad Dec 05 '19 at 12:50
  • @LajosArpad record is correct i am getting only one 1 column in record set which is presented in first select query. – Veshraj Joshi Dec 05 '19 at 12:56
  • I can't *see* anything *procedural* here. Why do you want/need a procedure? – Strawberry Dec 05 '19 at 12:57
  • @Strawberry thanks for the comment. I will have more select statements on above code snippet along with data manipulations and wanted to return limited data. So that I could reduce number of connections and reduce processing resources at non database server. Am I going in wrong direction sir? – Veshraj Joshi Dec 05 '19 at 13:03
  • User defined variables are not the same as local variables https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – P.Salmon Dec 05 '19 at 13:28
  • Are meeting_room_id, slot_id, min_booking_length actual columns of some tables? If so, which tables? – Lajos Arpad Dec 05 '19 at 13:29
  • @LajosArpad meeting_room_booked_slots.meeting_room_id and meeting_rooms.min_booking_length are actual columns. – Veshraj Joshi Dec 05 '19 at 14:33
  • @P.Salmon I got that how can I return OUT variables only when stored procedure has select statement/s? – Veshraj Joshi Dec 05 '19 at 14:36

0 Answers0