PHPからPDOを使ってSQLServerのストアドプロシージャを呼び出す際にOUTPUTが効かなくなる現象への対応

ドはまりしてしまったので、後学のためにメモを残します。

事象

次のようなスクリプトで、データ投入用のテーブルおよびプロシージャを作成します。

-- データ投入用のテーブル作成
create table test_table (
  col1 varchar(50),
  col2 int,
);

-- プロシージャ作成
create procedure sample
  @in varchar(50),
  @out int OUTPUT
as
  set @out = 567;
  insert into test_table values (@in, @out);
;

PDOから次のようなスクリプトでプロシージャを実行します。

$pdo = new PDO($dsn, $username, $password);
$stmt = $pdo->prepare('{CALL sample (?, ?)}'); // 'EXEC sample ?, ?'でもOK
$in = 'xyz';
$stmt->bindParam(1, $in, PDO::PARAM_STR);
$stmt->bindParam(2, $out, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();
echo $out;

プロシージャ内でset @out = 567と記述したので$outには567が設定されており、結果として567が出力される想定でしたが、結果が異なりました。

プロシージャがうまく動いているかどうか確認するため、作成したテーブルに格納されたデータを取得したところ、想定通りのデータが入っており、特に問題なさそうでした。

select * from test_table; -- 「'xyz', 567」が取得できる

ここで、プロシージャ内のinsert文をコメントアウトしたところ、想定通りのOUTPUT変数の使い方が出来ました。

-- プロシージャ作成
alter procedure sample
  @in varchar(50),
  @out int OUTPUT
as
  set @out = 567;
  -- insert into test_table values (@in, @out);
;

$pdo = new PDO($dsn, $username, $password);
$stmt = $pdo->prepare('{CALL sample (?, ?)}'); // 'EXEC sample ?, ?'でもOK
$in = 'xyz';
$stmt->bindParam(1, $in, PDO::PARAM_STR);
$stmt->bindParam(2, $out, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();
echo $out; //「567」が出力される

解決策

プロシージャ内で SET NOCOUNT ON の記述を入れることで、PHP側の変数に値が入るようになります。

-- プロシージャ作成
alter procedure sample
  @in varchar(50),
  @out int OUTPUT
as
  set @out = 567
  SET NOCOUNT ON
  insert into test_table values (@in, @out)
;

$pdo = new PDO($dsn, $username, $password);
$stmt = $pdo->prepare('{CALL sample (?, ?)}'); // 'EXEC sample ?, ?'でもOK
$in = 'xyz';
$stmt->bindParam(1, $in, PDO::PARAM_STR);
$stmt->bindParam(2, $out, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();
echo $out; //「567」が出力される

SET NOCOUNT ONとは

Transact-SQL ステートメントまたはストアド プロシージャで処理された行数を示すメッセージが結果セットの一部として返されないようにします。

参考

この記事のおかげで助かりました。
https://trentrichardson.com/making-sense-of-stored-procedures-with-php-pdo-and-sqlsrv.html

You also may notice the empty result set at position 0. This is the insert query (not the first select query as it is simply assigning a value). To suppress these you should use "SET NOCOUNT ON" in your stored procedure. At that point insert, update and deletes will not return these empty sets.