php 如何使用sqlsrv和“？”在php中执行存储过程 风格参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31575135/
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
How to execute a stored procedure in php using sqlsrv and "?" style parameters
提问by LittleTreeX
I've looked over several other questions that seem (from the titles) the same as this. However, my case is a bit different.
我查看了其他几个看起来（从标题中）与此相同的问题。但是，我的情况有点不同。
The following works (i.e. I get "success" and my database performs what I expect when running the procedure with the given variables):
以下工作（即我获得“成功”并且我的数据库在使用给定变量运行该过程时执行了我的期望）：
$sql = "MyDB.dbo.myProcedure {$var1}, {$var2}, {$var3}";
$result = sqlsrv_query($myConn, $sql);
if (!$result) {
echo 'Your code is fail.';
}
else {
echo 'Success!';
}
I want to avoid (or lessen the possibility of) SQL injection by creating the SQL string using parameters. For example:
我想通过使用参数创建 SQL 字符串来避免（或减少）SQL 注入。例如：
$sql = "select * from aTable where col1 = ? AND col2 = ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2));
//please note. This code WILL work!
But when I do that with a stored procedure it fails. It fails with no errors reported via sqlsrv_errors(), no action taken in database, and $result === false.
但是当我使用存储过程执行此操作时，它失败了。它失败了，没有通过 sqlsrv_errors() 报告错误，在数据库中没有采取任何行动，并且$result === false.
To be clear, the following fails:
需要明确的是，以下失败：
$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2, $var3));
Likewise a prepare/execute statement created the same way will also fail:
同样，以相同方式创建的准备/执行语句也会失败：
$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$stmt = sqlsrv_prepare($myConn, $sql, array(&$var1, &$var2, &$var3));
foreach($someArray as $key => $var3) {
if(sqlsrv_execute($stmt) === false) {
echo 'mucho fail.';
}
}
//this code also fails.
For completeness, I have confirmed that the stored procedure in question works directly within SQL Management Studio AND if called the way I mentioned above. Likewise, I have confirmed that I canuse parameterized queries for any raw query (like an insert, select, update vs a stored procedure).
为了完整起见，我已经确认有问题的存储过程直接在 SQL Management Studio 中工作，并且如果按照我上面提到的方式调用。同样，我已经确认我可以对任何原始查询（如插入、选择、更新与存储过程）使用参数化查询。
So, my question is how can I call a stored procedure using the parameterized query vs embedding the variables in the query string?
所以，我的问题是如何使用参数化查询与在查询字符串中嵌入变量来调用存储过程？
More importantly, I am actually wanting to use a prepare/execute, so hopefully the answer will allow this to work as well.
更重要的是，我实际上想使用准备/执行，所以希望答案也能让它起作用。
回答by chris85
The user contributions on the php.net have a write up on how to execute a stored procedure using the sqlsrv-prepare.
php.net 上的用户贡献有一篇关于如何使用 sqlsrv-prepare 执行存储过程的文章。
In case that is removed from the php.net user contributions in the future here is what it had(has) listed:
如果将来从 php.net 用户贡献中删除，这里是它已经（已经）列出的：
$procedure_params = array(
array(&$myparams['Item_ID'], SQLSRV_PARAM_OUT),
array(&$myparams['Item_Name'], SQLSRV_PARAM_OUT)
);
// EXEC the procedure, {call stp_Create_Item (@Item_ID = ?, @Item_Name = ?)} seems to fail with various errors in my experiments
$sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);
Here's the manual's page, http://php.net/manual/en/function.sqlsrv-prepare.php
这是手册的页面，http://php.net/manual/en/function.sqlsrv-prepare.php
回答by AndyD273
This is a follow up to the answer by @chris85.
这是@chris85 回答的后续。
It's worth noting here that once the statement is prepared, you need to execute it:
这里值得注意的是，一旦语句准备好，就需要执行它：
$sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);
if (!sqlsrv_execute($stmt)) {
echo "Your code is fail!";
die;
}
while($row = sqlsrv_fetch_array($stmt)){
//Stuff
}
sqlsrv_execute()only returns true/false. If you want to parse the data returned by the stored procedure you can process it just like the result from sqlsrv_query().
sqlsrv_execute()只返回真/假。如果您想解析存储过程返回的数据，您可以像处理sqlsrv_query().
If you forget the sqlsrv_execute()you'll get an error saying that the result has to be executed before it can be used.
如果你忘记了，sqlsrv_execute()你会得到一个错误，说必须先执行结果才能使用它。
回答by David Northcutt
Make sure you set this or you will always get errors returned if the stored procedure has messages being returned.
确保你设置了这个，否则如果存储过程有消息返回，你总是会返回错误。
sqlsrv_configure('WarningsReturnAsErrors',0);
//Full working code below
$sql = "{call NameOfDatabase.NameOfOwner.StoredProcedureName(?,?)}";
$params = array($param1, $param2);
if ($stmt = sqlsrv_prepare($conn, $sql, $params)) {
echo "Statement prepared.<br><br>\n";
} else {
echo "Statement could not be prepared.\n";
die(print_r(sqlsrv_errors(), true));
}
if( sqlsrv_execute( $stmt ) === false ) {
die( print_r( sqlsrv_errors(), true));
}else{
print_r(sqlsrv_fetch_array($stmt));
}
