SQL Server のストアドプロシージャを作成する
SQL Server のストアドプロシージャを作成する
SQL Server のストアドプロシージャには、ユーザー定義ストアドプロシージャ、システムストアドプロシージャ、拡張ユーザー定義ストアドプロシージがあります。
今回は、ユーザー定義ストアドプロシージャを作成してみましょう。
こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。
ユーザー定義ストアドプロシージャ
ユーザー定義ストアドプロシージャを作成するには CREATE PROCEDURE を使います。 シンプルなユーザー定義のストアドプロシージャを作成する構文は次の通りです。
CREATE PROCEDURE [ ストアドプロシージャ名 ]
[ @入力パラメーター名 1 ] [ 入力パラメーター名 1 のデータ型 ],
[ @入力パラメーター名 2 ] [ 入力パラメーター名 2 のデータ型 ],
[ @入力パラメーター名 3 ] [ 入力パラメーター名 3 のデータ型 ],
..
AS
BEGIN
[ ストアドプロシージャで処理したい一連の T-SQL ステートメント ]
END;
[ ストアドプロシージャで処理したい一連の T-SQL ステートメント ] には、ユーザー定義関数とは違って、テーブルの値を挿入・変更・削除等のスコープ外のリソースを変更するステートメントを書くことができます。
入力パラメーターには、また本サイトの T-SQL 入門では説明していませんが 「 ユーザー定義テーブル型 」 も指定することができます。
それでは、ユーザー定義ストアドプロシージャを作ってみましょう。
StudentID を入力パラメータとして受け取り、TestResult テーブルにその StudentID のデータが存在すれば、アウトプットパラメータで 「 TestResult が存在するので削除できません。 」 というメッセージを返し、存在していなければ Student テーブルから、その StudentID のレコードを削除する uspStudentDelete という名前のストアドプロシージャを作ってみます。
CREATE PROCEDURE uspStudentDelete
@StudentID INT,
@ErrorMessage NVARCHAR(100) OUT
AS
BEGIN
IF EXISTS
(SELECT *
FROM TestResult
WHERE StudentID = @StudentID)
BEGIN
SET @ErrorMessage = N'TestResult が存在するので削除できません。';
END
ELSE
BEGIN
DELETE
FROM Student
WHERE StudentID = @StudentID;
END
END;
アウトプットパラメタの後ろには OUTPUT または OUT というキーワードをつけます。
上記のスクリプトを実行すると、ユーザー定義ストアドプロシージャ uspStudentDelete ができました。
それでは、作ったユーザー定義のストアドプロシージャを使ってみましょう。
現在の Student テーブルと TestResult テーブルのデータは次のようになっています。
TestResult テーブルにデータが存在する、StudentID = 1 のデータを削除してみます。
DECLARE @ErrorMessage NVARCHAR(100);
EXEC uspStudentDelete
1,
@ErrorMessage OUTPUT;
PRINT @ErrorMessage;
[ 実行結果 ]
「 TestResult が存在するので削除できません。 」 というメッセージが返ってきました。
ここの呼び出し側で OUTPUT のキーワードを忘れると、エラーは出ずに実行され、ただ戻り値が取得できないのでご注意ください。
次に、TestResult テーブルにデータが存在しない、StudentID = 6 のデータを削除してみます。
DECLARE @ErrorMessage NVARCHAR(100);
EXEC uspStudentDelete
6,
@ErrorMessage OUTPUT;
PRINT @ErrorMessage;
[ 実行結果 ]
エラーメッセージは返ってこずに、Student テーブルのレコードが削除されましたね。
次は、SQL Server のストアドプロシージャを変更・削除してみましょう。