省略可能な引数を持つストアドプロシージャの作成 [SQLServer]

ストアド プロシージャの引数は必ず必要とは限りません。
例えば、SQLServerに標準に用意されているストアド プロシージャの中にも、引数を省略できるシステム ストアド プロシージャが数多くあります。
そこで今回は、ユーザー定義のストアド プロシージャを作成する際に省略可能な引数を定義する方法について紹介したいと思います。

省略可能な引数を持つシステム ストアド プロシージャの例

省略可能な引数を持つストアド プロシージャを作成していく前に、SQLServerに用意されているシステム ストアド プロシージャの中で、省略可能な引数を持つ例を見てみます。

ここでは数あるプロシージャの中から、テーブルの情報を取得する「sys.sp_tables」を例として取り上げます。

sp_tables プロシージャ

sp_tablesには以下の5つの引数があり、すべて省略可能になっています。

  1. @table_name nvarchar(384)
  2. @table_owner nvarchar(384)
  3. @table_qualifier sysname
  4. @table_type varchar(100)
  5. @fUsePattern bit

すべての引数を省略してsp_tablesを呼び出すと

すべてのテーブルの情報が返されます。

実行結果

引数を指定せずにsp_tablesを呼び出す

次に例えばテーブル名を指定して呼び出すと

指定したテーブルの情報のみ返ってきます。

実行結果

引数にテーブル名を指定してsp_tablesを呼び出す

最後にテーブルオーナー(スキーマ名)を指定して呼び出してみます。

指定したテーブルオーナーの情報のみ返してくれます。

実行結果

引数にテーブルオーナーを指定してsp_tablesを呼び出す

SQLServerに用意されている省略可能な引数を持つストアド プロシージャを見ることで概略はつかめたと思いますので、実際に引数を省略できるストアド プロシージャを作成していきます。

省略可能な引数を定義したストアド プロシージャの作成例

ここでは省略可能な引数を持つストアド プロシージャを作成しますが、処理は単純なものにします。
プロシージャは@PARAM1 AS INTと@PARAM2 AS INTと@PARAM3 AS INTの3つの引数を取り、@PARAM3を省略可能にします。
以下がサンプルプロシージャのSQLになります。

省略可能な引数を定義したストアド プロシージャの使用例

作成したストアド プロシージャを使用(実行)していきます。
省略可能な引数の@PARAM3を省略しない場合と省略した場合の2パターンについて記載します。

引数を省略しない場合

引数を省略しない場合は、省略可能な引数がないものと同じなので、特に問題はないと思います。

実行結果

省略可能な引数を定義したストアド プロシージャの引数を省略せずに呼び出す

引数を省略する場合

引数を省略する場合は、引数自体を記述しません。

実行結果

省略可能な引数を定義したストアド プロシージャの引数を省略して呼び出す
ここでは、省略可能な引数が定義順の最後の引数なので単純に省略していますが、省略可能な引数が定義の途中にある場合は呼び出し方が変わります。

上記で作成したストアド プロシージャを改変して第2引数のPARAM2が省略可能な場合を試してみます。

このプロシージャをすべての引数を指定して呼び出してみます。

これは問題なくデータを取得することができます。(結果は 1 + 3 + 5 = 9)

次に、第2引数まで指定して呼び出してみます。

実行結果

省略可能な引数を定義したストアド プロシージャのすべての引数を省略せずに呼び出す
エラーが発生しました。
第2引数の@PARAM2は省略できますが順番が真ん中なので、上記のSQLでは第3引数の@PARAM3が省略されたことになっています。

このような場合は省略可能でない引数の@PARAM3の引数名を指定して呼び出すか、省略可能な引数の@PARAM2に「DEFAULT」キーワードを指定するかします。
「DEFAULT」キーワードを使用することで、その引数はパラメーターの既定値を使用します。

実行結果

省略可能な引数を定義したストアド プロシージャの引数名を指定またはDEFAULTを指定して呼び出す
引数名を指定、または「DEFAULT」キーワードを指定することで、結果が取得できることが確認できました。

SQLServerではユーザー定義の関数を作成する際にも、省略可能な引数を定義することができます。

省略可能な引数を持つ関数については以下の記事を参照してください。

省略可能な引数を持つ関数の作成 [SQLServer]
SQLServerではユーザーが定義する独自の関数をデータベース内に作成することができます。 ユーザー定義関数は、値を返すスカラー関数やテ...