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 ...
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: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: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.The variable usage is obviously excess. W/o it this SP contains one stmt hence BEGIN-END and DELIMITER are excess too.
Also
month_name
andinput_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 inregion
column.