- 一番簡単な形（引数、戻り値なし）
- 入力引数ありの場合
- 入力引数ありの場合（呼び出し時に省略可能な引数）
- 出力引数ありの場合
- 戻り値ありの場合
- 上記をすべて組み合わせた形
一番簡単な形（引数、戻り値なし）
引数も戻り値も無い、一番簡単な形は次の通りです。TEST_PROC という名前のストアドプロシージャを作成しています。
| sql（実行可能なサンプルコード） |
1
2
3
| CREATE PROCEDURE TEST_PROC
AS
PRINT 'ここに処理を記述'; |
開始、終了を明確にする為に BEGIN END を付けても OK です。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
| CREATE PROCEDURE TEST_PROC
AS
BEGIN
PRINT 'ここに処理を記述';
END |
上記を実行する方法は次の通りです。
| sql（実行可能なサンプルコード） |
1
| EXEC TEST_PROC; |
上記の実行結果は次の通りです。
入力引数ありの場合
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
| CREATE PROCEDURE TEST_PROC
(
@ITEM_NAME AS VARCHAR(50)
, @ITEM_PRICE AS DECIMAL(12,0)
)
AS
PRINT @ITEM_NAME + 'の価格を表示';
PRINT @ITEM_PRICE; |
開始、終了を明確にする為に BEGIN END を付けても OK です。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
| CREATE PROCEDURE TEST_PROC
(
@ITEM_NAME AS VARCHAR(50)
, @ITEM_PRICE AS DECIMAL(12,0)
)
AS
BEGIN
PRINT @ITEM_NAME + 'の価格を表示';
PRINT @ITEM_PRICE;
END |
上記を実行する方法は次の通りです。
| sql（実行可能なサンプルコード） |
1
| EXEC TEST_PROC N'りんご',100; |
上記の実行結果は次の通りです。
入力引数ありの場合（呼び出し時に省略可能な引数）
SQL Server では、省略可能な引数を設定することができます。次のように、値が渡されなかった時に設定する値を指定しておくことで、その引数は省略可能となります。この例では、第二引数（@VAR2）は省略可能です。省略した場合、@VAR2 には NULL が設定されるようにしています。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| CREATE PROCEDURE PROC_TEST
(
@VAR1 AS INTEGER
, @VAR2 AS INTEGER = NULL
)
AS
PRINT 'VAR1の値は・・・';
PRINT @VAR1;
IF @VAR2 IS NULL
BEGIN
PRINT 'VAR2は値がセットされていません。';
END
ELSE
BEGIN
PRINT 'VAR2の値は・・・';
PRINT @VAR2;
END |
開始、終了を明確にする為に BEGIN END を付けても OK です。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| CREATE PROCEDURE PROC_TEST
(
@VAR1 AS INTEGER
, @VAR2 AS INTEGER = NULL
)
AS
BEGIN
PRINT 'VAR1の値は・・・';
PRINT @VAR1;
IF @VAR2 IS NULL
BEGIN
PRINT 'VAR2は値がセットされていません。';
END
ELSE
BEGIN
PRINT 'VAR2の値は・・・';
PRINT @VAR2;
END
END |
上記を実行する方法は次の通りです。２回目の呼び出しでは、第二引数を省略しています。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
| PRINT '引数を2個渡した場合';
EXEC PROC_TEST 100,200;
PRINT ''
PRINT '引数を1個だけ渡した場合';
EXEC PROC_TEST 500; |
上記の実行結果は次の通りです。
| 実行結果 |
1
2
3
4
5
6
7
8
9
10
| 引数を2個渡した場合
VAR1の値は・・・
100
VAR2の値は・・・
200
引数を1個だけ渡した場合
VAR1の値は・・・
500
VAR2は値がセットされていません。 |
出力引数ありの場合
呼び出し元がストアドプロシージャから値を受け取りたい場合、出力引数を使うことができます。出力引数を定義する場合、引数の後ろに OUTPUT を付与します。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
| CREATE PROCEDURE TEST_PROC
(
@ITEM_NAME AS VARCHAR(50) OUTPUT
, @ITEM_PRICE AS DECIMAL(12,0) OUTPUT
)
AS
SET @ITEM_NAME = '乗用車';
SET @ITEM_PRICE = 1500000; |
開始、終了を明確にする為に BEGIN END を付けても OK です。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
| CREATE PROCEDURE TEST_PROC
(
@ITEM_NAME AS VARCHAR(50) OUTPUT
, @ITEM_PRICE AS DECIMAL(12,0) OUTPUT
)
AS
BEGIN
SET @ITEM_NAME = '乗用車';
SET @ITEM_PRICE = 1500000;
END |
上記を実行する方法は次の通りです。事前にストアドプロシージャから値を受け取る引数を DECLARE しておき、呼び出し時に OUTPUT を付けて引数として指定します。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
| DECLARE @NAME AS VARCHAR(50);
DECLARE @PRICE AS DECIMAL(12, 0);
EXEC TEST_PROC @NAME OUTPUT,@PRICE OUTPUT;
PRINT @NAME + 'の価格は・・・';
PRINT @PRICE; |
上記の実行結果は次の通りです。
| 実行結果 |
1
2
| 乗用車の価格は・・・
1500000 |
戻り値ありの場合
ストアドプロシージャは RETURN を使うことで戻り値（リターンコード）を返すことができます。戻り値には整数（INTEGER）のみ指定可能です。その為、戻り値の型宣言はありません。
| sql（実行可能なサンプルコード） |
1
2
3
4
| CREATE PROCEDURE TEST_PROC
AS
PRINT '処理を実行';
RETURN 9; |
開始、終了を明確にする為に BEGIN END を付けても OK です。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
| CREATE PROCEDURE TEST_PROC
AS
BEGIN
PRINT '処理を実行';
RETURN 9;
END |
上記を実行する方法は次の通りです。事前にストアドプロシージャから戻り値を受け取る引数を DECLARE しておき、呼び出し時に戻り値を受け取ります。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
| DECLARE @RETURN_CODE AS INTEGER;
EXEC @RETURN_CODE = TEST_PROC;
PRINT 'ストアドプロシージャからの戻り値は・・・';
PRINT @RETURN_CODE; |
上記の実行結果は次の通りです。
| 実行結果 |
1
2
3
| 処理を実行
ストアドプロシージャからの戻り値は・・・
9 |
上記をすべて組み合わせた形
入力引数、出力引数、戻り値の全てがある場合のサンプルコードは次の通りです。入力引数で渡した年月の翌月の年月を出力引数で返します。年月に変換できない文字列が渡された場合は戻り値に 1 を、正常終了の場合は戻り値に 0 を返します。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| CREATE PROCEDURE GET_NEXT_MONTH
(
@YM AS CHAR(6)
, @NEXT_YM AS CHAR(6) OUTPUT
)
AS
BEGIN TRY
SET @NEXT_YM = LEFT(CONVERT(VARCHAR,DATEADD(MONTH, 1, @YM + '01'), 112), 6);
END TRY
BEGIN CATCH
PRINT '日付に変換できない年月が指定されました';
RETURN 1;
END CATCH
RETURN 0; |
開始、終了を明確にする為に BEGIN END を付けても OK です。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| CREATE PROCEDURE GET_NEXT_MONTH
(
@YM AS CHAR(6)
, @NEXT_YM AS CHAR(6) OUTPUT
)
AS
BEGIN
BEGIN TRY
SET @NEXT_YM = LEFT(CONVERT(VARCHAR,DATEADD(MONTH, 1, @YM + '01'), 112), 6);
END TRY
BEGIN CATCH
PRINT '日付に変換できない年月が指定されました';
RETURN 1;
END CATCH
RETURN 0;
END |
上記を実行する方法は次の通りです。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
| DECLARE @NEXT_YM AS CHAR(6);
DECLARE @RETURN_CODE AS INTEGER;
EXEC @RETURN_CODE = GET_NEXT_MONTH '201812', @NEXT_YM OUTPUT;
PRINT 'ストアドプロシージャからの戻り値は・・・';
PRINT @RETURN_CODE;
PRINT '出力引数から受け取った値は・・・'
PRINT @NEXT_YM; |
上記の実行結果は次の通りです。
| 実行結果 |
1
2
3
4
| ストアドプロシージャからの戻り値は・・・
0
出力引数から受け取った値は・・・
201901 |
引数に不正な値を渡す場合の実行例と実行結果は次の通りです。
| sql（実行可能なサンプルコード） |
1
2
3
4
5
6
7
8
9
10
| DECLARE @NEXT_YM AS CHAR(6);
DECLARE @RETURN_CODE AS INTEGER;
--ありえない年月を指定
EXEC @RETURN_CODE = GET_NEXT_MONTH '201813', @NEXT_YM OUTPUT;
PRINT 'ストアドプロシージャからの戻り値は・・・';
PRINT @RETURN_CODE;
PRINT '出力引数から受け取った値は・・・'
PRINT @NEXT_YM; |
| 実行結果 |
1
2
3
4
| 日付に変換できない年月が指定されました
ストアドプロシージャからの戻り値は・・・
1
出力引数から受け取った値は・・・ |