Laravel return empty array after stored procedure call

Question

I'm using Laravel 5.6 and I have the following code

 $values = [1, "2018-08-10", "2018-08-11"];
 $data = DB::select('exec SP_NAME ?, ?', $values);

 dump($data);

And this is my stored procedure:

ALTER PROCEDURE [dbo].[SP_NAME]
@Opc int,
@FechaIni nvarchar(20)=null,
@FechaFin nvarchar(20)=null
AS
BEGIN
    if @opc=1
    begin
        SELECT * from table where convert(date, datetimeField) between @FechaIni and @FechaFin
    end
END

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

Testing and playing with the code I found that the problem is when I'm sending more than one parameter, for example I change my select query for this:

SELECT * from table where convert(date, datetimeField) between '2018-08-10' and '2018-08-11'

What could be the problem and how can I solve it?

I think you forgot an extra ?

Felippe Duarte
– Felippe Duarte

2018-08-13 21:15:57 +00:00
Commented Aug 13, 2018 at 21:15 — Felippe Duarte
– Felippe Duarte, Commented Aug 13, 2018 at 21:15

squillman · Accepted Answer · 2018-08-13 21:17:40Z

1

I'm not familiar with Laravel but it looks like you're creating a value array with 3 values but you're only sending 2 parameters in your query. Should it maybe be

$data = DB::select('exec SP_NAME ?, ?, ?', $values);

You don't get an error from SQL Server because you define @FechaIni and @FechaFin as optional parameters.

answered Aug 13, 2018 at 21:17

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

1 Comment

User1899289003 Over a year ago

That's correct, I think that after solving another errors I forgot to add that parameter haha thanks!

Collectives™ on Stack Overflow

Laravel return empty array after 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