連載：VS 2005でいってみようDBプログラミング 第8回　Let's Master ストアド・プロシージャ！（前編） 山田 祥寛（http://www.wings.msn.to/） 2006/09/30
Page1 Page2 Page3 Page4

ストアド・プロシージャに条件分岐を追加する

　以上でストアド・プロシージャの基本的な構文、利用方法については理解できたと思います。しかし、前項で紹介した程度の機能ならば、なにもストアド・プロシージャを利用するまでもなく、単一のパラメータ付きSQL命令でも十分です。そこで今度は、もう少し複雑な――ストアド・プロシージャの利用価値が理解できるようなサンプル・アプリケーションを作成してみましょう。

　先ほどのサンプル・アプリケーションは、初期状態（DropDownListコントロールが未選択状態）ではページ上に何も表示されなかったはずですが、ここでは入力パラメータ「@publish_id」の値をストアド・プロシージャで判定し、値が0（未選択）の場合にBookテーブル内のデータを全件取り出すようにします。

図8　改良したsp.aspxの実行結果

プルダウン・メニュー未選択状態のときには、全出版社の書籍情報を一覧表示する。

　それではさっそく、前項で作成したストアド・プロシージャ「SelectProc」をサーバ・エクスプローラから開き、リスト2のように編集してみましょう。

ALTER PROCEDURE SelectProc   @publish_id INT AS   IF @publish_id=0     BEGIN       SELECT * FROM         Book AS b       INNER JOIN         Publish AS p       ON         b.publish_id=p.publish_id     END   ELSE     BEGIN       SELECT * FROM         Book AS b       INNER JOIN         Publish AS p       ON         b.publish_id=p.publish_id       WHERE         b.publish_id=@publish_id     END

リスト2　入力パラメータによって条件分岐するストアド・プロシージャの定義

　このように、ストアド・プロシージャでは通常のプログラミング言語と同様、条件分岐や繰り返しなどの構文を利用することが可能です。これら「フロー制御構文」を利用することで、単一のSQL命令ではなかなか表現しにくい複雑なロジックを記述できます。

　IF〜ELSE命令の一般的な構文は、以下のとおりです。

IF 条件式   条件式がTRUEの場合に実行されるTransact-SQL命令 [ELSE   条件式がFALSEの場合に実行されるTransact-SQL命令]

IF〜ELSE命令の構文

　リスト2では、それぞれIF／ELSEブロック配下のTransact-SQL命令をBEGIN〜ENDブロックでくくっていますが、これはBEGIN〜ENDブロックで囲まれたブロックが一塊であることを示すものです。リスト2のようにIF／ELSEブロック配下のTransact-SQL命令が1つのみである場合には、BEGINとENDを省略しても構いません。

　ここでは、@public_idパラメータが0である（つまり、出版社が指定されていない）場合に、Bookテーブルに登録された全書籍情報を、0以外の（出版社が指定された）場合には、指定された出版社に属する書籍情報のみを取得します。

　なお、リスト2ではすでに存在するSelectProcプロシージャを「変更」しますので、CREATE PROCEDURE命令ではなく、ALTER PROCEDURE命令を使用している点にも注目してください。

　以上を理解したら、先ほど作成したsp.aspxを再度実行してみましょう。今度は初期状態で書籍情報が全件表示されることが確認できるはずです。

ストアド・プロシージャから出力パラメータを取得する

　ストアド・プロシージャでは、アプリケーション側からパラメータを引き渡すだけではありません。「出力パラメータ」を利用することで、ストアド・プロシージャで処理した結果値を、アプリケーションに引き渡すことも可能になります。

　ここでは、前項で作成したSelectProcプロシージャを修正し、SELECT命令で取得した結果件数を、出力パラメータを介して.aspxファイルに引き渡し、表示してみましょう。

図9　取得したレコード件数をページ上部に表示

　出力パラメータを利用する手順は、以下のとおりです。

［1］ストアド・プロシージャSelectProcを修正する

　前項同様、ストアド・プロシージャ「SelectProc」をサーバ・エクスプローラから開き、リスト3のように追加してみましょう。太字部分が追記個所です。

ALTER PROCEDURE SelectProc   @publish_id INT,   @num INT OUTPUT AS   IF @publish_id=0     BEGIN       ……中略……     END   ELSE     BEGIN       ……中略……     END   SELECT @num = @@ROWCOUNT

リスト3　出力パラメータを利用するストアド・プロシージャの定義

　出力パラメータを定義するには、データ型の後方に「OUTPUT」キーワードを指定する必要があります。

　出力パラメータ（変数）に対して値をセットするのは、SELECT命令の役割です。SELECTの代わりにSET命令を利用することも可能です。@@ROWCOUNT関数はSQL Serverで用意されたシステム関数の1つで、直前のSQL命令によって取得された行数を表します。

［2］パラメータを定義する

　出力パラメータを利用するには、SqlDataSourceコントロールの側でも出力パラメータを明示的に登録しておく必要があります。

　フォーム・デザイナで、SqlDataSourceコントロール（IDは「proc_sds」）のタスク・メニューから［データソースの構成］を選択します。すでに設定済みのデータソース構成ウィザードが開きますので、［パラメータの定義］ダイアログまで進めます。

図10　［パラメータの定義］ダイアログ

これは［パラメータ］欄からnumパラメータを選択し、［詳細設定プロパティの表示］リンクをクリックした後の画面。

　［パラメータ］欄からnumパラメータを選択したうえで、［詳細設定プロパティの表示］リンクをクリックします。すると、パラメータ（Parameterオブジェクト）の詳細プロパティが表示されますので、ここではDirectionプロパティを「Output」に設定します。これによって、numパラメータは出力パラメータと見なされ、.aspxファイルから適宜参照することが可能になります。

　Directionプロパティはパラメータの種類を表すためのプロパティで、以下のような設定値を指定可能です。ReturnValue値（戻り値）については、あらためて後述します。

設定値 概要 Input 入力パラメータ InputOutput 入力／出力パラメータ Output 出力パラメータ

表6　Directionプロパティの設定値（ParameterDirection列挙体の値）

［3］出力パラメータの値を取得する

　出力パラメータの値を取得するには、SqlDataSourceコントロールのSelectedイベント・ハンドラを利用します。Selectedイベント*3は選択コマンドの実行「直後」に発生するイベントで、主に処理結果の取得やエラー・ハンドルなどを行うために使用します。

*3 ちなみに、SqlDataSourceコントロールには、ほかにもInserting／Inserted、Updating／Updated、Deleting／Deleted、Selecting／Selectedイベントが用意されており、それぞれのコマンド実行前後で行うべき処理をプログラムから規定できるようになっています。

Selectedイベント・ハンドラの具体的なコードは、以下のリスト4のとおりです。

Protected Sub proc_sds_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)   result.Text = _     e.Command.Parameters("@num").Value & "件を取得しました" End Sub

リスト4　ストアド・プロシージャの出力パラメータを取得／表示するコード（sp.aspx）

　データソース・コントロール（SqlDataSourceコントロール）で定義されたコマンドにアクセスするには、イベント・ハンドラの第2パラメータとして受け取ったSqlDataSourceStatusEventArgsオブジェクトからCommandプロパティを使用します。これによって、データソース・コントロールで定義されたコマンドをDbCommandオブジェクトとして取得できますので、そのParameterプロパティを介して取得したDbParameterCollectionオブジェクトから個々のパラメータにアクセスできます。

　以上を理解したら、sp.aspxを実際に実行してみましょう。本項冒頭の図9のように現在表示されているレコードの件数の表示が確認できれば成功です。

INDEX
	Visual Studio 2005でいってみようDBプログラミング
	第8回　Let's Master ストアド・プロシージャ！（前編）
	1．ストアド・プロシージャ基本のキ（1）
	2．ストアド・プロシージャ基本のキ（2）
	3．ストアドに条件分岐を追加する／ストアドから出力パラメータを取得する
	4．ストアド・プロシージャから戻り値を取得する

　

「Visual Studio 2005でいってみようDBプログラミング」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）