MySQL Stored Procedure Issue with Procedure Variables-CodePudding

I have this stored procedure

DELIMITER $$
USE `testdb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestProcedure`(
    IN year_number YEAR,
    IN month_name VARCHAR(12),
    IN input_region VARCHAR(20)
)
BEGIN
    DECLARE total_regions_count INT DEFAULT 0;
    
    ## Get Distinct Regions.
    SELECT
        total_regions_count = COUNT(DISTINCT region)
    FROM aws_cost AS AC
    WHERE AC.year = year_number;
    
    SELECT total_regions_count;
 

END$$

DELIMITER ;

When I call this stored procedure total_regions_count I get is 0 but when I execute the query directly, I get the correct count of distinct values (Which is 9 and not 0.).

Why is the variable returning me default value of the variable as the result? Why 9 is not getting returned?

I have tried INTO keyword also to set the value but still the same result.

SELECT COUNT(DISTINCT region) INTO total_regions_count ...

CodePudding user response：

It doesn't work properly inside the stored procedure because you're attempting to make a comparison between the value stored in "total_regions_count" (defaulted to 0) and COUNT(DISTINCT region), which is a number bigger than 0. Since the comparison fails, you get the value 0.

Try instead using INTO as follows:

DELIMITER $$
USE `testdb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestProcedure`(
    IN year_number YEAR,
    IN month_name VARCHAR(12),
    IN input_region VARCHAR(20)
)
BEGIN
    DECLARE total_regions_count INT DEFAULT 0;
    
    ## Get Distinct Regions.
    SELECT COUNT(DISTINCT region) INTO total_regions_count
    FROM aws_cost AS AC
    WHERE AC.year = year_number;
    
    SELECT total_regions_count;
 

END$$

DELIMITER ;

CodePudding user response：

In MySQL expressions, = is an equality comparison operator, not an assignment operator.

You could use := if you want to make an assignment in an expression. But this syntax is now discouraged.

The preferred syntax is to use SELECT ... INTO to assign the results of queries to variables:

SELECT
    COUNT(DISTINCT region)
FROM aws_cost AS AC
WHERE AC.year = year_number
INTO total_regions_count;

Is it possible that the count of matching rows is in fact zero? You could test this by making the default value of total_regions_count some other value like -1. Then you'll be sure that the value is overwritten by the count computed by your query.