ストアド プロシージャの引数は必ず必要とは限りません。
例えば、SQLServerに標準に用意されているストアド プロシージャの中にも、引数を省略できるシステム ストアド プロシージャが数多くあります。
そこで今回は、ユーザー定義のストアド プロシージャを作成する際に省略可能な引数を定義する方法について紹介したいと思います。
目次
省略可能な引数を持つシステム ストアド プロシージャの例
省略可能な引数を持つストアド プロシージャを作成していく前に、SQLServerに用意されているシステム ストアド プロシージャの中で、省略可能な引数を持つ例を見てみます。
ここでは数あるプロシージャの中から、テーブルの情報を取得する「sys.sp_tables」を例として取り上げます。
sp_tables プロシージャ
sp_tablesには以下の5つの引数があり、すべて省略可能になっています。
- @table_name nvarchar(384)
- @table_owner nvarchar(384)
- @table_qualifier sysname
- @table_type varchar(100)
- @fUsePattern bit
すべての引数を省略してsp_tablesを呼び出すと
|
1 2 |
-- 引数を指定せずにsp_tablesを呼び出す EXEC sys.sp_tables |
すべてのテーブルの情報が返されます。
次に例えばテーブル名を指定して呼び出すと
|
1 2 |
-- 引数にテーブル名を指定してsp_tablesを呼び出す EXEC sys.sp_tables 'DatabaseLog' |
指定したテーブルの情報のみ返ってきます。
最後にテーブルオーナー（スキーマ名）を指定して呼び出してみます。
|
1 2 |
-- 引数にテーブルオーナーを指定してsp_tablesを呼び出す EXEC sys.sp_tables @table_owner = 'dbo' |
指定したテーブルオーナーの情報のみ返してくれます。
SQLServerに用意されている省略可能な引数を持つストアド プロシージャを見ることで概略はつかめたと思いますので、実際に引数を省略できるストアド プロシージャを作成していきます。
省略可能な引数を定義したストアド プロシージャの作成例
ここでは省略可能な引数を持つストアド プロシージャを作成しますが、処理は単純なものにします。
プロシージャは@PARAM1 AS INTと@PARAM2 AS INTと@PARAM3 AS INTの3つの引数を取り、@PARAM3を省略可能にします。
以下がサンプルプロシージャのSQLになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* * 3つの値を加算した結果を取得するプロシージャ * * 引数 * @PARAM1 AS INT * @PARAM2 AS INT * @PARAM3 AS INT 省略可能 (省略時 = 0) */ CREATE PROCEDURE USP_SAMPLE ( @PARAM1 AS INT, @PARAM2 AS INT, @PARAM3 AS INT = 0 ) AS BEGIN SELECT @PARAM1 + @PARAM2 + @PARAM3; RETURN; END |
省略可能な引数を定義したストアド プロシージャの使用例
作成したストアド プロシージャを使用（実行）していきます。
省略可能な引数の@PARAM3を省略しない場合と省略した場合の2パターンについて記載します。
引数を省略しない場合
引数を省略しない場合は、省略可能な引数がないものと同じなので、特に問題はないと思います。
|
1 2 |
-- 第3引数を省略せずに呼び出す EXEC USP_SAMPLE 1, 3, 5; |
引数を省略する場合
引数を省略する場合は、引数自体を記述しません。
|
1 2 |
-- 第3引数を省略して呼び出す EXEC USP_SAMPLE 1, 3; |
ここでは、省略可能な引数が定義順の最後の引数なので単純に省略していますが、省略可能な引数が定義の途中にある場合は呼び出し方が変わります。
上記で作成したストアド プロシージャを改変して第2引数のPARAM2が省略可能な場合を試してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/* * 3つの値を加算した結果を取得するプロシージャ EX * * 引数 * @PARAM1 AS INT * @PARAM2 AS INT 省略可能 (省略時 = 0) * @PARAM3 AS INT */ CREATE PROCEDURE USP_SAMPLE_EX ( @PARAM1 AS INT, @PARAM2 AS INT = 0, @PARAM3 AS INT ) AS BEGIN SELECT @PARAM1 + @PARAM2 + @PARAM3; RETURN; END |
このプロシージャをすべての引数を指定して呼び出してみます。
|
1 2 |
-- すべての引数を省略せずに呼び出す EXEC USP_SAMPLE_EX 1, 3, 5; |
これは問題なくデータを取得することができます。（結果は 1 + 3 + 5 = 9）
次に、第2引数まで指定して呼び出してみます。
|
1 2 |
-- すべての引数を省略せずに呼び出す EXEC USP_SAMPLE_EX 1, 3; |
エラーが発生しました。
第2引数の@PARAM2は省略できますが順番が真ん中なので、上記のSQLでは第3引数の@PARAM3が省略されたことになっています。
このような場合は省略可能でない引数の@PARAM3の引数名を指定して呼び出すか、省略可能な引数の@PARAM2に「DEFAULT」キーワードを指定するかします。
「DEFAULT」キーワードを使用することで、その引数はパラメーターの既定値を使用します。
|
1 2 3 4 5 |
-- 引数名@PARAM3を指定 EXEC USP_SAMPLE_EX 1, @PARAM3 = 5; -- DEFAULTを指定 EXEC USP_SAMPLE_EX 1, DEFAULT, 5; |
引数名を指定、または「DEFAULT」キーワードを指定することで、結果が取得できることが確認できました。
SQLServerではユーザー定義の関数を作成する際にも、省略可能な引数を定義することができます。
省略可能な引数を持つ関数については以下の記事を参照してください。
