問題描述

我想從我的 Laravel 模型中的 SQL Server 存儲過程獲取輸出參數，該存儲過程在 SQL Server 管理工具和 NORMAL php 文件中運行良好，但在 Laravel 模型中不起作用.

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.

這是我在 Laravel 模型中執行存儲過程的方式:

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

    $id              =   "123454";
    $email           =   "ABC@ABC.com";
    $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
                            ");

在 Laravel 中，當我嘗試運行存儲過程時出現以下錯誤:

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.

這是我在正常運行的普通 PHP 中使用的方式:

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

    $id              =   "123454";
    $email           =   "ABC@ABC.com";
    $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;

我是否必須在 SP 中選擇返回參數，以便我們可以循環并在 LARAVEL MODEL 中獲取它.

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

推薦答案

非常感謝大家.

發布此答案，因為它對在 Web 開發過程中受到打擊的人很有用.

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

在做了大量的研究和嘗試了很多事情之后，我知道答案就在我的問題的底部，我建議在我的存儲過程的末尾添加 SELECT ，它將把值返回給我的 Laravel模型.

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

在 Laravel 模型/控制器中調用這個存儲過程:

Calling this Stored Procedure in Laravel Model/Controller:

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

在 Laravel 模型中訪問返回變量:

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.

這篇關于Laravel 模型 SQL Server:從存儲過程中獲取輸出參數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！