Calling stored procedures in Laravel 5.5 using SQL Server with output

Question

I have the following stored procedure:

declare @CodigoRet int

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

Select @CodigoRet

OBS: the LancaContaContabil is my table and the Codigo is the PK for that table

And I'm trying to call this procedure in Laravel 5.5 but it gives me the error: "The active result for the query contains no fields":

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

Same error using the variant without dbraw:

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

Also tried with statement, but the return is always 1:

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

EDIT: I created the procedure under the name 'testeproc' and now i've tried this, with the same error as above:

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

What am I doing wrong?

score 3 · Accepted Answer · answered Sep 29 '17 at 18:35

I just got it sorted out. I will post 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 is to use "SET NOCOUNT ON" before calling the procedure. I found this here

score 2 · Answer 2 · answered Sep 27 '17 at 15:38

2

try this:

Without parameter:

$results = DB::select('EXEC your_stored_procedure');

Wit parameters

$results = DB::select('exec your_stored_procedure("parameter1", "parameter2",..)');

or

$results = DB::select('exec your_stored_procedure(?,?,..)',array($parameter1,$parameter2));

answered Sep 27 '17 at 15:38

Alessandro Minoccheri

35,521
22
122
171

Thanks for the reply Alessandro, but i've already tried this and the error is the same "The active result for the query contains no fields". Now i've just created a simple procedure to test, with just one select and its working fine. But the output one, still failing. – fjurr Sep 27 '17 at 17:17
@Alessandro Minoccheri Maybe you cn help me. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent – moses toh Aug 15 '18 at 02:35

score 1 · Answer 3 · answered Sep 02 '18 at 05:49

Here is a sample Stored Procedure:

CREATE PROCEDURE ReturnIdExample
    (
             @paramOne int
            ,@paramTwo nvarchar(255)
    )
    AS

    SET NOCOUNT ON; --IMPORTANT!

    BEGIN

    -- Grab the id that was just created
    DECLARE @ObjectID int;

    INSERT INTO [Table]
    (
             [ColumnNameA]
            ,[ColumnNameB]
    ) VALUES (
             @paramOne
            ,@paramTwo
    )

    SET @ObjectID = SCOPE_IDENTITY();

    -- Select the id to return it back to laravel
    SELECT@ObjectID AS ObjectID;

END

Calling this Stored Procedure in Laravel Model/Controller:

$submit = DB::select("EXEC ReturnIdExample ?,?", array( $paramOne ,$paramTwo ) );

Accessing the Return Variable in Laravel Model:

return $submit[0]->ObjectId;

Calling stored procedures in Laravel 5.5 using SQL Server with output

3 Answers3