Laravel 6 return empty array after MySql stored procedure call

Question

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?

Bill Karwin · Accepted Answer · 2022-10-31 14:56:21Z

3

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.

answered Oct 31, 2022 at 14:56

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alan delgado Over a year ago

Thanks, works fine do not using @ sigil. But I've the same problem when i pase Year argument to stored procedure. CREATE DEFINER PROCEDURE pr_SegOrder(date_fin datetime, time_year INTEGER)

Collectives™ on Stack Overflow

Laravel 6 return empty array after MySql stored procedure call

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related