MySQL Stored Procedure Issue with Procedure Variables

S7H 0
Viewed 32

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 ...

stored-procedures mysql
3 Answers

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 ;
userQZpQ0

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.

Bill Karwin0

The variable usage is obviously excess. W/o it this SP contains one stmt hence BEGIN-END and DELIMITER are excess too.

CREATE DEFINER=`root`@`localhost` PROCEDURE `TestProcedure`(
    IN year_number YEAR,
    IN month_name VARCHAR(12),
    IN input_region VARCHAR(20)
)
    SELECT COUNT(DISTINCT region) AS total_regions_count
    FROM aws_cost AS AC
    WHERE AC.year = year_number;

Also month_name and input_region parameters are used nowhere and hense they're excess too.

You use SELECT variable = expression. This is SQL Server (MS SQL) syntax. In MySQL this construction performs another task - not assignment but compare. With obvious FALSE result - the variable value is zero whereas the expression will return zero only when there exists at least one row in this table and all rows in the table contains NULL in region column.

Akina0
Related