Call stored Procedure in the Laravel using SQL Server with OUTPUT

Question

I’m trying to call a stored file by Laravel 5.5 but I’m not getting it. I’m using SQL Server.

Follow the stored Precedent:

declare @CodigoRet int

exec Generator 'LancaContaContabil', @Codigo = @CodigoRet output

Select @CodigoRet

I researched a lot and first tried the simplest way:

$results = DB::select(DB::raw('DECLARE @CodigoRet INT; execute Generator 
\'LancaContaContabil\', @Codigo = @CodigoRet OUTPUT;'));
echo $results;

The above code gives the following error: "The active result for the query contains in Fields".

I tried also with the statement, but it only returns 1, follows below:

$results = DB::statement('DECLARE @CodigoRet INT; EXEC Generator 
\'LancaContaContabil\', @Codigo = @CodigoRet OUTPUT;');
echo $results;
die;

I also tried creating the Procedure and calling it, but it gives the same problem ("The active result for the query contains no Fields"), as if there were no data returning from select:

$results = DB::select('EXECUTE testeproc');
echo $results;
die;

I’m doing something wrong or is there an easier way to call this stored Process on Windows?

Answer 1

I managed to solve it. I will leave here the solution that can help other people with the same problem.

$dbh = DB::connection()->getPdo();
$sth = $dbh->prepare("SET NOCOUNT ON; EXEC ProcLancaContaContabil");
$sth->execute();
$codigo = $sth->fetchAll(PDO::FETCH_COLUMN, 0);

The trick was to put the "SET NOCOUNT ON" before calling the trial.

I found him here