Get values from Select statement and Insert them in Temp table Stored procedure [closed]

Question

Here is my stored procedure.

I want to select data from a table, save it in a variable then insert in other variable. However, my select statement has where. In it the value for the day should come from the for loop. This means in the for loop I will get DAYNAME(dateTime) for a particular day. On the basis of that I want to fetch values and use it in a insert statement.

DROP PROCEDURE IF EXISTS home_cleaner.getAvailableDateAndTime;

CREATE PROCEDURE home_cleaner.`getAvailableDateAndTime`(dateTime date)
  BEGIN
  DECLARE dateTime               datetime DEFAULT NOW();
  DECLARE dateTimeCounter        int DEFAULT 0;
  DECLARE dateTimeCounterLimit   int DEFAULT 90;
  DECLARE result                 varchar(255) DEFAULT NULL;
  DECLARE result1                varchar(255) DEFAULT NULL;
  DECLARE result2                varchar(255) DEFAULT NULL;
  DECLARE result3                varchar(255) DEFAULT NULL;
  DECLARE result4                varchar(255) DEFAULT NULL;

  DECLARE
     c CURSOR FOR SELECT GROUP_CONCAT(
                            cleaner_calender.from,
                            "-",
                            cleaner_calender.to)
                            AS result
                    FROM cleaner_calender
                   WHERE day = DAYNAME(dateTime) AND status = "1";



  DROP TABLE IF EXISTS foo;
  CREATE TABLE foo
  (
     id         int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     date1      date NULL,
     dayNameP   varchar(20) NULL,
     timings    varchar(255) NULL
  )
  ENGINE = innodb;

OPEN c;
  WHILE dateTimeCounter < dateTimeCounterLimit
  DO
     // here i want to fetch result1 for the dateTime which will be incrementing
     FETCH c INTO result1;

     INSERT INTO foo(date1, dayNameP, timings)
     VALUES (dateTime, DAYNAME(dateTime), result1);

     SET dateTime = DATE_ADD(dateTime, INTERVAL 1 DAY);
     SET dateTimeCounter = dateTimeCounter + 1;
  END WHILE;    
 CLOSE c;
 SELECT * FROM foo;
END;

EDITED

--
-- Table structure for table `cleaner_calender`
--

CREATE TABLE IF NOT EXISTS `cleaner_calender` (
`cleaner_calender_id` bigint(20) unsigned NOT NULL,
`cleaner_id` bigint(20) unsigned NOT NULL,
`day`    enum('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') NOT   NULL,
 `from` time NOT NULL,
`to` time NOT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT '1',
`created_at` int(11) unsigned NOT NULL,
`updated_at` int(11) unsigned DEFAULT NULL,
`created_by` bigint(20) unsigned NOT NULL,
`updated_by` bigint(20) unsigned DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cleaner_calender`
--

INSERT INTO `cleaner_calender` (`cleaner_calender_id`, `cleaner_id`, `day`,  `from`, `to`, `status`, `created_at`, `updated_at`, `created_by`, `updated_by`)   VALUES
(2, 13, 'Sunday', '00:30:00', '02:00:00', 2, 1435488637, 1435488637, 22, NULL),
(3, 13, 'Sunday', '00:30:00', '02:00:00', 2, 1435488725, 1435488725, 22, NULL),
(4, 13, 'Sunday', '00:30:00', '02:00:00', 2, 1435488770, 1435488770, 22,   NULL),
(5, 13, 'Monday', '02:00:00', '02:30:00', 2, 1435488771, 1435488771, 22, NULL),


 --
 -- Indexes for table `cleaner_calender`
 --
 ALTER TABLE `cleaner_calender`
 ADD PRIMARY KEY (`cleaner_calender_id`), ADD KEY `created_by`  (`created_by`), ADD KEY `updated_by` (`updated_by`), ADD KEY `cleaner_id` (`cleaner_id`), ADD KEY `created_at` (`created_at`);

 --
 -- AUTO_INCREMENT for dumped tables
 --

 --
 -- AUTO_INCREMENT for table `cleaner_calender`
 --
 ALTER TABLE `cleaner_calender`
 MODIFY `cleaner_calender_id` bigint(20) unsigned NOT NULL    AUTO_INCREMENT,AUTO_INCREMENT=54;
 --
 -- Constraints for dumped tables
 --

 --
 -- Constraints for table `cleaner_calender`
 --
 ALTER TABLE `cleaner_calender`
 ADD CONSTRAINT `cleaner_calender_ibfk_1` FOREIGN KEY (`created_by`)    REFERENCES `users` (`user_id`) ON UPDATE NO ACTION,
 ADD CONSTRAINT `cleaner_calender_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON UPDATE NO ACTION,
 ADD CONSTRAINT `cleaner_calender_ibfk_3` FOREIGN KEY (`cleaner_id`) REFERENCES `cleaners` (`cleaner_id`) ON UPDATE NO ACTION;

Answer 1

The problem was my select query was returning more than one row, and my code was not handling them via a "loop" so it was not working and giving me errors.

This is my new Procedure...

BEGIN
  DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler.

  DECLARE dateTime                  datetime DEFAULT NOW();
  DECLARE dateTimeCounter           int DEFAULT 0;
  DECLARE dateTimeCounterLimit      int DEFAULT 90;
  DECLARE result                    varchar(255) DEFAULT NULL;

  DECLARE resultTimePeriod          varchar(255) DEFAULT NULL;
  DECLARE rsultTimeDiffH            int DEFAULT NULL;
  DECLARE resultUnavailiability     varchar(255) DEFAULT NULL;
  DECLARE resultDate                date DEFAULT NULL;
  DECLARE resultPossible            int DEFAULT NULL;
  DECLARE resultPossibleStartTime   time DEFAULT NULL;


  DECLARE
     c CURSOR FOR SELECT CONCAT(cleaner_calender.from,
                                "-",
                                cleaner_calender.to)
                            AS result,
                         TIME_TO_SEC(
                            TIMEDIFF(cleaner_calender.to,
                                     cleaner_calender.from))
                            AS timeDiffH,
                         CONCAT(cleaner_unavailability.start_time,
                                "-",
                                cleaner_unavailability.end_time)
                            AS unavailiability,
                         cleaner_unavailability.`date`,
                         CASE
                            WHEN     cleaner_unavailability.start_time
                                        IS NOT NULL
                                 && cleaner_unavailability.end_time
                                       IS NOT NULL
                            THEN
                               CASE
                                  WHEN     (    TIME_TO_SEC(
                                                   TIMEDIFF(
                                                      cleaner_unavailability.start_time,
                                                      "00:00:00")) >=
                                                   TIME_TO_SEC(
                                                      TIMEDIFF(
                                                         cleaner_calender.from,
                                                         "00:00:00"))
                                            && TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.start_time,
                                                     "00:00:00")) <
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.to,
                                                        "00:00:00")))
                                       && (    TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.end_time,
                                                     "00:00:00")) >
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.from,
                                                        "00:00:00"))
                                           && TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_unavailability.end_time,
                                                    "00:00:00")) <=
                                                 TIME_TO_SEC(
                                                    TIMEDIFF(
                                                       cleaner_calender.to,
                                                       "00:00:00")))
                                  THEN
                                     CASE
                                        WHEN TIME_TO_SEC(
                                                TIMEDIFF(
                                                   cleaner_unavailability.start_time,
                                                   cleaner_calender.from)) >=
                                                TIME_TO_SEC(
                                                   TIMEDIFF(
                                                      cleaner_calender.to,
                                                      cleaner_unavailability.end_time))
                                        THEN
                                           TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.start_time,
                                                 cleaner_calender.from))
                                        ELSE
                                           TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_calender.to,
                                                 cleaner_unavailability.end_time))
                                     END
                                  WHEN     (    TIME_TO_SEC(
                                                   TIMEDIFF(
                                                      cleaner_unavailability.start_time,
                                                      "00:00:00")) >=
                                                   TIME_TO_SEC(
                                                      TIMEDIFF(
                                                         cleaner_calender.from,
                                                         "00:00:00"))
                                            && TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.start_time,
                                                     "00:00:00")) <
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.to,
                                                        "00:00:00")))
                                       && (TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) >
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.to,
                                                    "00:00:00")))
                                  THEN
                                     TIME_TO_SEC(
                                        TIMEDIFF(
                                           cleaner_unavailability.start_time,
                                           cleaner_calender.from))
                                  WHEN     (TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) <
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.from,
                                                     "00:00:00")))
                                       && (    TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.end_time,
                                                     "00:00:00")) >
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.from,
                                                        "00:00:00"))
                                           && TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_unavailability.end_time,
                                                    "00:00:00")) <=
                                                 TIME_TO_SEC(
                                                    TIMEDIFF(
                                                       cleaner_calender.to,
                                                       "00:00:00")))
                                  THEN
                                     TIME_TO_SEC(
                                        TIMEDIFF(
                                           cleaner_calender.to,
                                           cleaner_unavailability.end_time))
                                  WHEN     (TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) <
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.from,
                                                     "00:00:00")))
                                       && (TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) >
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.to,
                                                    "00:00:00")))
                                  THEN
                                     "STEP 4"
                                  WHEN (    TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) >=
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.to,
                                                     "00:00:00"))
                                        && TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) >=
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.to,
                                                    "00:00:00")))
                                  THEN
                                     TIME_TO_SEC(
                                        TIMEDIFF(cleaner_calender.to,
                                                 cleaner_calender.from))
                                  WHEN (    TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) <
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.from,
                                                     "00:00:00"))
                                        && TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) <
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.from,
                                                    "00:00:00")))
                                  THEN
                                     TIME_TO_SEC(
                                        TIMEDIFF(cleaner_calender.to,
                                                 cleaner_calender.from))
                                  ELSE
                                     "STEP LAST"
                               END
                            ELSE
                               TIME_TO_SEC(
                                  TIMEDIFF(cleaner_calender.to,
                                           cleaner_calender.from))
                         END
                            AS possible,
                         CASE
                            WHEN     cleaner_unavailability.start_time
                                        IS NOT NULL
                                 && cleaner_unavailability.end_time
                                       IS NOT NULL
                            THEN
                               CASE
                                  WHEN     (    TIME_TO_SEC(
                                                   TIMEDIFF(
                                                      cleaner_unavailability.start_time,
                                                      "00:00:00")) >=
                                                   TIME_TO_SEC(
                                                      TIMEDIFF(
                                                         cleaner_calender.from,
                                                         "00:00:00"))
                                            && TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.start_time,
                                                     "00:00:00")) <
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.to,
                                                        "00:00:00")))
                                       && (    TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.end_time,
                                                     "00:00:00")) >
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.from,
                                                        "00:00:00"))
                                           && TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_unavailability.end_time,
                                                    "00:00:00")) <=
                                                 TIME_TO_SEC(
                                                    TIMEDIFF(
                                                       cleaner_calender.to,
                                                       "00:00:00")))
                                  THEN
                                     CASE
                                        WHEN TIME_TO_SEC(
                                                TIMEDIFF(
                                                   cleaner_unavailability.start_time,
                                                   cleaner_calender.from)) >=
                                                TIME_TO_SEC(
                                                   TIMEDIFF(
                                                      cleaner_calender.to,
                                                      cleaner_unavailability.end_time))
                                        THEN
                                           cleaner_calender.from
                                        ELSE
                                           cleaner_unavailability.end_time
                                     END
                                  WHEN     (    TIME_TO_SEC(
                                                   TIMEDIFF(
                                                      cleaner_unavailability.start_time,
                                                      "00:00:00")) >=
                                                   TIME_TO_SEC(
                                                      TIMEDIFF(
                                                         cleaner_calender.from,
                                                         "00:00:00"))
                                            && TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.start_time,
                                                     "00:00:00")) <
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.to,
                                                        "00:00:00")))
                                       && (TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) >
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.to,
                                                    "00:00:00")))
                                  THEN
                                     cleaner_calender.from
                                  WHEN     (TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) <
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.from,
                                                     "00:00:00")))
                                       && (    TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_unavailability.end_time,
                                                     "00:00:00")) >
                                                  TIME_TO_SEC(
                                                     TIMEDIFF(
                                                        cleaner_calender.from,
                                                        "00:00:00"))
                                           && TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_unavailability.end_time,
                                                    "00:00:00")) <=
                                                 TIME_TO_SEC(
                                                    TIMEDIFF(
                                                       cleaner_calender.to,
                                                       "00:00:00")))
                                  THEN
                                     cleaner_unavailability.end_time
                                  WHEN     (TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) <
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.from,
                                                     "00:00:00")))
                                       && (TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) >
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.to,
                                                    "00:00:00")))
                                  THEN
                                     0
                                  WHEN (    TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) >=
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.to,
                                                     "00:00:00"))
                                        && TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) >=
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.to,
                                                    "00:00:00")))
                                  THEN
                                     cleaner_calender.from
                                  WHEN (    TIME_TO_SEC(
                                               TIMEDIFF(
                                                  cleaner_unavailability.start_time,
                                                  "00:00:00")) <
                                               TIME_TO_SEC(
                                                  TIMEDIFF(
                                                     cleaner_calender.from,
                                                     "00:00:00"))
                                        && TIME_TO_SEC(
                                              TIMEDIFF(
                                                 cleaner_unavailability.end_time,
                                                 "00:00:00")) <
                                              TIME_TO_SEC(
                                                 TIMEDIFF(
                                                    cleaner_calender.from,
                                                    "00:00:00")))
                                  THEN
                                     cleaner_calender.from
                                  ELSE
                                     0
                               END
                            ELSE
                               cleaner_calender.from
                         END
                            AS possibleStartTime
                    FROM cleaner_calender
                         LEFT JOIN cleaner_unavailability
                            ON     cleaner_calender.cleaner_id =
                                      cleaner_unavailability.cleaner_id
                               AND cleaner_unavailability.status = "1"
                               AND cleaner_unavailability.`date` = DATE(dateTime)
                   WHERE     cleaner_calender.status = "1"
                         AND day = DAYNAME(dateTime)
                  HAVING possible > seconds;


  -- Declaring continue handler for the cursor stg_product_data
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 


  DROP TABLE IF EXISTS foo;
  CREATE TABLE foo
  (
     id         int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     date1      date NULL,
     dayNameP   varchar(20) NULL,
     timings    varchar(255) NULL,
     tresultTimePeriod    varchar(255) NULL,
     trsultTimeDiffH    varchar(255) NULL,
     tresultUnavailiability    varchar(255) NULL,
     tresultDate    varchar(255) NULL,
     tresultPossible    varchar(255) NULL,
     tresultPossibleStartTime    varchar(255) NULL
  )
  ENGINE = innodb;


  WHILE dateTimeCounter < dateTimeCounterLimit
  DO

    OPEN c;
    read_loop:
     LOOP                                      -- Loop through the records
        FETCH c
             INTO resultTimePeriod,
                  rsultTimeDiffH,
                  resultUnavailiability,
                  resultDate,
                  resultPossible,
                  resultPossibleStartTime;

        IF v_done
        THEN         -- Checking if the cursor has fetched the last record
           LEAVE read_loop;        -- Exit if last record had been fetched
        ELSE


--                IF resultPossible IS NOT NULL
--                THEN
               INSERT INTO foo(date1, dayNameP, timings, 
               tresultTimePeriod, trsultTimeDiffH, 
               tresultUnavailiability, tresultDate, tresultPossible,
               tresultPossibleStartTime)
               VALUES (dateTime, DAYNAME(dateTime), resultPossible,
               resultTimePeriod, rsultTimeDiffH,
               resultUnavailiability, resultDate, resultPossible,
               resultPossibleStartTime);
--                 END IF;


        END IF;
     END LOOP;

     SET dateTime = DATE_ADD(dateTime, INTERVAL 1 DAY);
     SET dateTimeCounter = dateTimeCounter + 1;
     SET v_done = FALSE ;

     CLOSE c;
  END WHILE;

 SELECT * FROM foo;

END