Laravel 6 return empty array after MySql stored procedure call-CodePudding

I'm using Laravel 6.2 and I have the following code in my controller

$date_ini='01-01-2022';
$date_fin='31-12-2022';

$seg=\DB::select('CALL pr_SegOrder(?,?,?)',[$client,$date_ini,$date_fin]);

return $seg;

And this is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_SegOrder`(`client` varchar(200), `date_ini` datetime, `date_fin` datetime)
BEGIN
 SELECT cod,norder 
FROM
  order op 
WHERE
cast(op.created_at as date) BETWEEN @date_ini AND @date_fin 

ORDER BY
    op.cod asc ;
END

If I execute my query on MySql everything works fine. When I execute the SP from Laravel there is no error message, just an empty result.

The problem only ocurring when i use date parameters, because if only send "client" parameter from laravel, works fine.

any idea what could occurring?

CodePudding user response：

In MySQL, procedure input arguments like date_ini do not use the @ sigil. That character is for user-defined session variables. They are not the same variable.

When you used the expression BETWEEN @date_ini AND @date_fin, those two variables probably have the value NULL, unless you had set their value in the session before calling the procedure.

So just use BETWEEN date_ini AND date_fin, without the @ characters. Then they will be references to the procedure input arguments.

Be careful not to name your procedure arguments the same as columns in the tables, or else your query will refer to the columns, not the variables.

CodePudding user response：

I've the same problem when i pase Year (number) argument to stored procedure.

> CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_SegOrder`(`date_fin` datetime,`year_date` INTEGER)
BEGIN
 SELECT cod,norder 
FROM
  order 
WHERE
CAST(fentrega_aprox AS DATE) < date_fin
    AND YEAR(created_at) = year_date

ORDER BY
    cod asc ;
END

If I execute my query on MySql everything works fine. When I execute the SP from Laravel there is no error message, just an empty result. My Laravel Code:

$year_date=2022;
$date_fin='2022-10-31';

$seg=\DB::select('CALL pr_SegOrder(?,?)',[$date_fin,$year_date]);

return $seg;