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?

fjurr · Accepted Answer · 2017-09-29 18:35:28Z

3

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

answered Sep 29, 2017 at 18:35

fjurr

5623 gold badges11 silver badges23 bronze badges

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

Comments

Alessandro Minoccheri · Accepted Answer · 2017-09-27 15:38:56Z

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, 2017 at 15:38

Alessandro Minoccheri

36.1k22 gold badges128 silver badges176 bronze badges

2 Comments

fjurr Over a year ago

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.

moses toh Over a year ago

@Alessandro Minoccheri Maybe you cn help me. Look at this : stackoverflow.com/questions/51838922/…

user8268874 · Accepted Answer · 2018-09-02 05:49:23Z

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;

Collectives™ on Stack Overflow

Calling stored procedures in Laravel 5.5 using SQL Server with output

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related