Laravel 4 mssql 存储过程，带参数作为准备语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20666490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel 4 mssql stored procedure with parameters as a prepared statements
提问by Priit
I would like to format this working query as a prepared statement:
我想将此工作查询格式化为准备好的语句：
Route::get('procedure/{adr}/{part}/{batch?}', function($adr, $part, $batch= '') {
return DB::connection('sqlconnection')->select("Procedure_Name '$adr', '$part', '$batch'");
});`
Tried everything I could think of and the closest I got was this.
尝试了我能想到的一切，我得到的最接近的是这个。
DB::connection('sqlconnection')->select('Procedure_Name ?, ?, ?', array("'$adr'", "'$part'", "'$batch'"));
This results in a error with and sql command that when used directly on the db works just fine.
这会导致和 sql 命令出现错误，当直接在 db 上使用时，它可以正常工作。
Illuminate \ Database \ QueryException SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Procedure_Name'. (SQL: Procedure_Name 'VLM', '7999800', 'P20131018-29')
Illuminate \ Database \ QueryException SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]'Procedure_Name' 附近的语法不正确。(SQL: Procedure_Name 'VLM', '7999800', 'P20131018-29')
What am I doing wrong?
我究竟做错了什么？
回答by Antonio Carlos Ribeiro
You might find some trouble to execute them.
您可能会发现执行它们时遇到一些麻烦。
Here are some options:
以下是一些选项：
DB::select('EXEC Procedure_Name ?,?,?',array($adr,$part,$batch));
Or
或者
DB::statement('EXEC Procedure_Name' . DB::raw($adr) . ',' . DB::raw($part) . ',' . DB::raw($batch) );
And, this is a quick and dirty way to do it:
而且，这是一种快速而肮脏的方法：
$db = DB::connection();
$stmt = $db->pdo->prepare("EXEC Procedure_Name ?,?,?");
$stmt->bindParam(1, $adr);
$stmt->bindParam(2, $part);
$stmt->bindParam(3, $batch);
$stmt->execute();
$search = array();
do {
$search = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');
} while ($stmt->nextRowset());
回答by Kamaro Lambert
I you are having error 7405 then do the following
如果您遇到错误 7405，请执行以下操作
Fixing Error-7405-Heterogeneous-queries-require-ANSI_NULLS
修复 Error-7405-Heterogeneous-queries-require-ANSI_NULLS
DB::statement('SET ANSI_NULLS ON; SET ANSI_WARNINGS ON');
$result = DB::select('EXEC sp_storeprocedure ' . DB::raw($param1) . ',' . DB::raw($param2) . ',' . DB::raw($param3) );
