Laravel Model SQL Server:从存储过程获取输出参数

本文介绍了Laravel Model SQL Server:从存储过程获取输出参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想从我的Laravel模型中的SQL Server存储过程中获取输出参数，该存储过程在SQL Server管理工具和NORMAL php文件中均能正常工作，但在LARAVEL MODEL中却无法工作.

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"; $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"; $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.

以下是示例存储过程:

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.