CODEWITHSUNDEEP
mysqlstored-proceduresphpmyadmin
sunlight76
sunlight76

Reputation: 103

phpMyAdmin and date parameter procedure not woring

I defined a procedure with phpMyAdmin. Without parameters it works great, but since I added paramenters it returns 0 rows.

defined procedure:

BEGIN
SELECT
    GROUP_CONCAT(
CONCAT("SUM(IF(descrizione='", descrizione ,"',1 ,0)) AS '", descrizione, "'"), "
"
    ) INTO @answers
FROM (
    SELECT DISTINCT descrizione FROM turni_all

) A;

SET @query := 
  CONCAT(
    'SELECT idvuds, ', @answers, 
    ' FROM turni_all WHERE turni_all.data_turno >= p_data_dal AND turni_all.data_turno <= p_data_al GROUP BY turni_all.idvuds'
  );

PREPARE statement FROM @query;
EXECUTE statement;
END

This is parameter definition: Screenshot

If I change p_data_al and p_data_dal in procedure definition with "2017-06-01" and "2017-06-05" (and only including double quotes) it works correctly.

it works ONLY in this way:

'FROM turni_all WHERE turni_all.data_turno >= "2017-06-01" AND turni_all.data_turno <= "2017-06-05" GROUP BY turni_all.idvuds'

turni_all.data_turno is defined as "date".

Upvotes: 1

Views: 400

Answers (1)

Shadow
Shadow

Reputation: 34234

The problem is that the parameters are defined in the stored procedure, but you attempt to use them in a prepared statement. The prepared statements do not run in the scope of the stored procedure (they run in session scope). Therefore prepared statements do not have access to any stored proc parameters, since the parameters go out of scope when the procedure completes.

You need to concatenate the values of the parameters directly into the prepared statement's sql code to work:

SET @query := 
    CONCAT(
'SELECT idvuds, ', @answers, 
' FROM turni_all WHERE turni_all.data_turno >=\'', p_data_dal,'\' AND turni_all.data_turno <= \'', p_data_al, '\' GROUP BY turni_all.idvuds'

);

Since the date literals appear as strings, I added escaped ' characters to enclose the values of the parameters.

Upvotes: 1

Related Questions