Laravel 模型 SQL Server:从存储过程中获取输出参数

2021-12-18 00:00:00 php laravel-5 sql-server stored-procedures output-parameter

我想从我的 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.

相关文章