php - Laravel Model SQL Server: Get Output Parameters from Stored Procedure

Question

I want to get the output parameter from my SQL Server stored procedure in the Model of my Laravel, The Stored Procedure is working perfectly in the SQL Server Management tool and also in NORMAL php file but it does not work in LARAVEL MODEL.

This is how I am executing the Stored Procedure in the Laravel Model:

    $id              =   "123454";
    $email           =   "[email protected]";
    $name            =   "FName, LName";
    $returnval        =   1; //My OUTPUT PARAMETER from SP
    $action       =   'ACTION_MESSAGE;
    $dummy            =   0;
    $client          =   $request->input('client');
    $team_l          =   $request->input('team');
    $contact         =   $request->input('contact');
    $team            =   $request->input('team_l');

    $Query        =  DB::select
                            (" SET NOCOUNT ON; SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; EXEC [MY_STORED_PROC]
                                        $client,
                                        $team,
                                        $contact,
                                        '$team_l',
                                        '$id',
                                        '$name',
                                        '$email',
                                        '$action',
                                        $dummy,
                                        $returnval
                            ");

In Laravel I am getting following error when I try to run the Stored Procedure:

(3/3) QueryException
SQLSTATE[IMSSP]: The active result for the query contains no fields.

This is how I was using in the Normal PHP which was working:

    $id              =   "123454";
    $email           =   "[email protected]";
    $name            =   "FName, LName";
    $returnval       =   1; //My OUTPUT PARAMETER from SP
    $action          =   'ACTION_MESSAGE;
    $dummy           =   0;
    $client          =   $_POST('client');
    $team            =   $_POST('team');
    $contact         =   $_POST('contact');
    $team_l          =   $_POST('team_l');

    $Query        = "{ CALL  My_PROC_NAME(?,?,?,?,?,?,?,?,?,?) }";
    $PARAMS           = array(   array($client, SQLSRV_PARAM_IN),
                                array($team,        SQLSRV_PARAM_IN),
                                array($contact, SQLSRV_PARAM_IN),
                                array($team_l,      SQLSRV_PARAM_IN),
                                array($id,         SQLSRV_PARAM_IN),
                                array($name,       SQLSRV_PARAM_IN),
                                array($email,       SQLSRV_PARAM_IN),
                                array($action,      SQLSRV_PARAM_IN),
                                array($dummy,       SQLSRV_PARAM_IN),
                                array($returnval,   SQLSRV_PARAM_OUT)
                            );              

        $result     =   sqlsrv_query($connect, $Query,$PARAMS);

My Stored Procedure and everything seems to be fine. I tried the following things keeping in the stored procedure as well but no luck:

SET NOCOUNT ON; 
SET ANSI_NULLS ON; 
SET ANSI_WARNINGS ON;

Do I have to SELECT the return parameter in the SP so we can loop and get it in the LARAVEL MODEL.

Answer 1

Thanks a lot everyone.

Posting this answer as it will be useful to someone who will get struck during their web development.

After doing lot of research and trying out numerous things I got to know the answer was lying at the bottom of my question itself where I had proposed to add SELECT at the end of my stored procedure which will return the value to my Laravel Model.

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;

It worked perfectly for me, Hope this will help you guys and you wont end up wasting lot of time on this just like I did. Have a great day.