I need to perform a full table level scan as it should be supposed to retrieve more than one rows based on the User ID.
Since I used to get sqlMessage stating 'Subquery returns more than 1 row'.
{ Error: ER_SUBQUERY_NO_1_ROW: Subquery returns more than 1 row
...
code: 'ER_SUBQUERY_NO_1_ROW',
errno: 1242,
sqlMessage: 'Subquery returns more than 1 row',
sqlState: '21000',
index: 0,
sql: 'CALL GetLocationTag(1)' }
Here is the call made to the GetLocationTag()
code
DELIMITER $$
CREATE
DEFINER = `root`@`localhost`
PROCEDURE `someDB`.`GetLocationTag`(IN `UserId` BIGINT)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'Retrieves Multiple Stored Location Tags, Address, Location, etc. based on User_ID'
BEGIN
DECLARE flag INT(11);
DECLARE locRecord VARCHAR(255);
IF EXISTS (SELECT * FROM `location_map` WHERE `user_id` = User_Id ) THEN
BEGIN
SELECT * INTO locRecord FROM `location_map` WHERE `user_id` = User_Id;
SET flag = 1;
END;
ELSE
SET flag = 0;
SET locRecord = 0;
END IF;
SELECT locRecord, flag;
END$$
DELIMITER ;
For the below query,
SELECT * INTO locRecord FROM `location_map` WHERE `user_id` = User_Id;
How am I need to retrieve all the rows in the form of arrays & store it into variable OR How am I suppose to code such that error message should need to be resolved?
Use a view to store your fetched data instead of a variable
DELIMITER $$
CREATE
DEFINER = `root`@`localhost`
PROCEDURE `someDB`.`GetLocationTag`(IN `User_Id` BIGINT)
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'Retrieves Multiple Stored Location Tags, Address, Location, etc. based on User_ID'
BEGIN
DECLARE flag INT(11);
DECLARE locRecord VARCHAR(255);
IF EXISTS (SELECT * FROM `location_map` WHERE `user_id` = User_Id ) THEN
BEGIN
SET @viewQry = CONCAT('CREATE OR REPLACE VIEW `someDB`.`temp` as SELECT *, 1 as flag FROM `location_map` WHERE `user_id` = ',User_Id,';');
PREPARE stmt FROM @viewQry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT * FROM temp;
END;
ELSE
SELECT 0 AS locRecord, 0 AS flag;
END IF;
END$$
DELIMITER ;