Laravel Model SQL Server:从存储过程获取输出参数 [英] Laravel Model SQL Server: Get Output Parameters from Stored Procedure
问题描述
我想从我的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.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.
以下是示例存储过程:
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 Model SQL Server:从存储过程获取输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
