Problem in a stored procedure if you try to pass variables using MySQL

Databases
1

I need to change a static query into a dynamic query in a stored procedure by associating the relative variables, using MySQL v5.

This is the static query

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
			'SUM( CASE WHEN CONVERT ( tNumber, UNSIGNED INTEGER ) > 200 AND DATE_FORMAT(tStartDate, ''%Y%m'') = 
			''', DATE_FORMAT(period, '%Y%m'), ''' THEN 1 ELSE 0 END) `', DATE_FORMAT(period, '%b %Y'), '`'	
    )		
  ) INTO @sql
FROM
(
SELECT DATE_FORMAT(tStartDate, '%Y-%m-01') period
	FROM dotable_2025
	WHERE DATE(tStartDate) BETWEEN MAKEDATE(year(now()),1)
  AND LAST_DAY(MAKEDATE( YEAR ( CURDATE()), 360 ))
	GROUP BY DATE_FORMAT(tStartDate, '%Y-%m-01')
) s;

This is the dynamic version where the table name changes from dotable_2025 to dotable_current_year

SET @a = CONCAT
('SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
			SUM( CASE WHEN CONVERT ( tNumber, UNSIGNED INTEGER ) > 200 AND DATE_FORMAT(tStartDate, ''%Y%m'') = 
			', DATE_FORMAT(period, '%Y%m'), ' THEN 1 ELSE 0 END) `', DATE_FORMAT(period, '%b %Y'), '`	
    )		
  ) INTO @sql
FROM
(
SELECT DATE_FORMAT(tStartDate, ''%Y-%m-01'') period
	FROM dotable_', YEAR(CURDATE()) ,'
	WHERE DATE(tStartDate) BETWEEN MAKEDATE(year(now()),1)
  AND LAST_DAY(MAKEDATE( YEAR ( CURDATE()), 360 ))
	GROUP BY DATE_FORMAT(tStartDate, ''%Y-%m-01'')
) s;');

PREPARE stmt FROM @a;
SET @a := NULL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But when I execute the stored procedure I have this error and I don’t understand what I’m doing wrong

Procedure execution failed
1054 - Unknown column 'period' in 'field list'
Time: 0,045s
2

Do a PRINT @a (or whatever the mySQL equivalent of it is…).

Compare that output to your static query and you should see where you made your mistake pretty quickly. If not, share the results of the print of @a and we can help you out…

3

Thanks for help.

When I try to print the query I always get the same error

SET @a = CONCAT
('SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
			SUM( CASE WHEN CONVERT ( tNumber, UNSIGNED INTEGER ) > 200 AND DATE_FORMAT(tStartDate, ''%Y%m'') = 
			', DATE_FORMAT(period, '%Y%m'), ' THEN 1 ELSE 0 END) `', DATE_FORMAT(period, '%b %Y'), '`	
    )		
  ) INTO @sql
FROM
(
SELECT DATE_FORMAT(tStartDate, ''%Y-%m-01'') period
	FROM dotable_', YEAR(CURDATE()) ,'
	WHERE DATE(tStartDate) BETWEEN MAKEDATE(year(now()),1)
  AND LAST_DAY(MAKEDATE( YEAR ( CURDATE()), 360 ))
	GROUP BY DATE_FORMAT(tStartDate, ''%Y-%m-01'')
) s;');


SELECT @a;

Procedure execution failed

1054 - Unknown column 'period' in 'field list'

Time: 0,045s
4

i mean… my immediate suspicion is all the single quotes floating around that look like they should be full quotation marks. have you copied this query fron somewhere?

5

Sorry, it’s Monday and I haven’t had enough caffeine yet. I knew why it was failing but it should have dawned on me it was failing on the set and not on the execute.

I’ll just tell you the issue - it’s the two places you have ', DATE_FORMAT(period, ‘%Y%m’), ’

The reason is that you don’t have a variable called period, so it’s failing to generate a valid concatenation.

This works, but there seems like there should be a better way to generate that query

SET @a = CONCAT
('SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
			SUM( CASE WHEN CONVERT ( tNumber, UNSIGNED INTEGER ) > 200 AND DATE_FORMAT(tStartDate, ''%Y%m'') = DATE_FORMAT(period, ''%Y%m'') THEN 1 ELSE 0 END), DATE_FORMAT(period, ''%b %Y''), 	
    )		
  ) INTO @sql
FROM
(
SELECT DATE_FORMAT(tStartDate, ''%Y-%m-01'') period
	FROM dotable_', YEAR(CURDATE()) ,'
	WHERE DATE(tStartDate) BETWEEN MAKEDATE(year(now()),1)
  AND LAST_DAY(MAKEDATE( YEAR ( CURDATE()), 360 ))
	GROUP BY DATE_FORMAT(tStartDate, ''%Y-%m-01'')
) s;');
6

Thanks a lot for help.

I have edit the query but

Procedure execution failed

1111 - Invalid use of group function

Time: 0,045s

Your last version

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
			SUM( CASE WHEN CONVERT ( tNumber, UNSIGNED INTEGER ) > 200 AND DATE_FORMAT(tStartDate, '%Y%m') = DATE_FORMAT(period, '%Y%m') THEN 1 ELSE 0 END), DATE_FORMAT(period, '%b %Y')	
    )		
  ) INTO @sql
FROM
(
SELECT DATE_FORMAT(tStartDate, '%Y-%m-01') period, tNumber, tStartDate
	FROM `dotable_2025`
	WHERE DATE(tStartDate) BETWEEN MAKEDATE(year(now()),1)
  AND LAST_DAY(MAKEDATE( YEAR ( CURDATE()), 360 ))
	GROUP BY DATE_FORMAT(tStartDate, '%Y-%m-01')
) s;

I never had this error in the static query

7

Hence why I thought that was way too complicated. If I understand what you’re looking for, this should give you the output you’re looking for…

SET @a = CONCAT('SELECT DATE_FORMAT(tStartDate, ''%Y-%m-01'') 
	 				  , SUM(CASE WHEN CONVERT (tNumber, UNSIGNED INTEGER) > 200 THEN 1 ELSE 0 END) 
     				  , DATE_FORMAT(tStartDate, ''%b %Y'') 
  				   FROM dotable_', YEAR(CURDATE()) ,'
 				  WHERE DATE(tStartDate) BETWEEN MAKEDATE(year(now()),1)
   					AND LAST_DAY(MAKEDATE(YEAR(CURDATE()), 360))
  				  GROUP BY DATE_FORMAT(tStartDate, ''%Y-%m-01'') 
     					 , DATE_FORMAT(tStartDate, ''%b %Y'')');