Laravel never ending EXEC

Question

I'm rewriting a program from pure PHP to Laravel and I have a problem with executing a stored procedure (I didn't write it).

When I try

$sheetLines = DB::select("exec XXXXXXX".dbo.PRICELIST '?'", [$id]); 

It keeps on going while PHP hasn't reached max memory. (increasing memory only makes it run longer)

Meanwhile, an old program it takes about 3 seconds and sends the response.

$tsql = "exec XXXXXXX.".dbo.PRICELIST '".$id."'";

Also when I'm calling other stored procedures from other modules everything works fine.

I have noticed that something like this also happens if I try for example:

DB::select(count(price) from orders group by price);
// would work with: count(price) as price_count

I have searched this problem a lot but have found no solution. I'll be thankful for any help

Answer 1

I would access the underlying PDO driver to execute the stored procedure.

Try

$db = DB::getPdo();

$stmt = $db->prepare("EXEC XXXXXXX.dbo.PRICELIST :id");
$stmt->bindValue(':id', $id);

$result = $stmt->execute();

If that fails to work you could try the query method;

$query = DB::getPdo()->query("EXEC XXXXXXX.dbo.PRICELIST $id");

Regarding your count issue, in Laravel's Eloquent you can do the following;

$count = DB::table('orders')->groupBy('price')->count('price');

Answer 2

I've little experience with PHP, but seen:

set nocount on

Cause oddball behavior in some MSSQL drivers. Especially a stored procedure returns a single recordset, but doesn't output a "(x row(s) affected)" type messages. (Messages can be seen when manually executing a query in SQL Server Management Studio.) My rule of thumb for complex stored procedure is to add...

set nocount on

...at the start of a stored procedure and conclude with...

set nocount off

....just before the final output.

Example:

create proc spTester 
as 
begin
set nocount off

-- {... do lots of crazy processing ...}

-- ok, ready to return the final output
set nocount off
select 1 as colA, 2 as colB

end