使用PHP PDO将参数传递给SQL Server存储过程 [英] Passing parameters to SQL Server stored procedures with PHP PDO

问题描述

我无法通过PDO将参数从PHP传递到SQL Server上的存储过程.

I'm having trouble passing parameters to a stored procedure on SQL Server from PHP with PDO.

我可以使用以下简化版本的代码对不需要参数的过程成功执行查询:

I can successfully perform a query on a procedure that requires no parameters, with code like this simplified version:

$dbConnection =  Craft::createComponent(array(
    'charset'           => 'utf8',
    'class'             => 'CDbConnection',
    'autoConnect'       => true,
));

$dbConnection->connectionString = 'sqlsrv:Server=MYSERVER;Database=My_Database';
$dbConnection->username = 'MyUsername';
$dbConnection->password = 'MyPasword';

$command = $dbConnection->createCommand(
    'MYSERVER.My_Database.My_Schema.my_Stored_Procedure'
);

$data = $command->queryAll();

这可以很好地返回数据.

This returns data fine.

但是，如果存储过程需要参数，则无法解决该问题.我尝试用以下内容替换createCommand行:

But if the stored procedure requires parameters, I can't work out how to do that. I've tried replacing the createCommand line with something like:

$command = $dbConnection->createCommand(
    "MYSERVER.My_Database.My_Schema.my_Stored_Procedure 1, 'A Parameter', '', ''"
);

使用我给出的四个参数，该参数应该可以工作，但这可以帮助我:

using the four parameters I've been given, that should work, but this gets me:

 CDbCommand failed to execute the SQL statement: SQLSTATE[IMSSP]: The active
 result for the query contains no fields.. The SQL statement executed was:
 MYSERVER.My_Database.My_Schema.my_Stored_Procedure 1, 'A Parameter', '', ''

我对SQL Server存储过程的语法不熟悉，没关系从PHP调用它们，所以我有些困惑.

I'm not familiar with the syntax for SQL Server stored procedures, never mind calling them from PHP, so I'm a bit stumped.

推荐答案

如果存储过程未返回任何内容，则必须使用SET NOCOUNT ON;

If your stored procedure does not return anything you have to use SET NOCOUNT ON;

您可以在此处了解更多信息:

You can read more about it here:

https://msdn.microsoft.com/en-us/library/ms189837.aspx

这篇关于使用PHP PDO将参数传递给SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！