Help us understand the problem. What is going on with this article?

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とは

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver15

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.

huge-book-storage
フリーランスやっています。主にJavaによるWebアプリ開発に携わっています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away