【SQL Server入門】ストアドプロシージャの作成と削除方法を徹底解説

プロシージャ（Procedure）とは、データベース内に保存しておき、後から再利用できる複数のステートメントで構成されたプログラムのことです。SQL Serverでは、プロシージャにパラメータを渡すことが可能です。関数のように特定の値を返すわけではありませんが、実行の成功・失敗を示す役割を持ちます。

本記事では、SQL Serverにおけるプロシージャの作成方法と削除方法について、基本構文と具体例を交えながらわかりやすく解説します。

CREATE PROCEDURE（プロシージャの作成）

構文

SQL Serverでプロシージャを作成するには、以下の構文を使用します。

CREATE { PROCEDURE | PROC } [schema_name.]procedure_name
    [@parameter [type_schema_name.] datatype
        [VARYING] [= default] [OUT | OUTPUT | READONLY]
    , @parameter [type_schema_name.] datatype
        [VARYING] [= default] [OUT | OUTPUT | READONLY]]

[WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause }]
[FOR REPLICATION]

AS
BEGIN
    [declaration_section]
    executable_section
END;

各パラメータの意味：

1. schema_name： プロシージャが所属するスキーマ名
2. procedure_name： 作成するプロシージャに割り当てる名前
3. @parameter： プロシージャに渡す1つ以上のパラメータ
4. type_schema_name： パラメータのデータ型が属するスキーマ名（該当する場合のみ）
5. datatype： @parameter のデータ型
6. default： @parameter に設定されるデフォルト値
7. OUT / OUTPUT： @parameter が出力パラメータであることを示す
8. READONLY： @parameter がプロシージャ内で上書きできないことを示す
9. ENCRYPTION： プロシージャのソースコードがシステム内にテキストとして保存されないようにする
10. RECOMPILE： このプロシージャのクエリがキャッシュされないようにする
11. EXECUTE AS句： プロシージャを実行するセキュリティコンテキストを指定する
12. FOR REPLICATION： レプリケーション処理中にのみプロシージャが実行されることを指定する

具体例

以下は、従業員名を出力する簡単なプロシージャの作成例です。

CREATE PROCEDURE spNhanvien
    @nhanvien_name VARCHAR(50) OUT
AS
BEGIN
    DECLARE @nhanvien_id INT;
    SET @nhanvien_id = 8;

    IF @nhanvien_id < 10
        SET @nhanvien_name = 'Smith';
    ELSE
        SET @nhanvien_name = 'Lawrence';
END;

上記の例では、spNhanvien という名前のプロシージャを作成しています。このプロシージャには出力パラメータ @nhanvien_name が定義されており、その戻り値は変数 @nhanvien_id の条件分岐によって決まります。IDが10未満の場合は「Smith」、それ以外の場合は「Lawrence」が出力されます。

作成したプロシージャは、以下のように呼び出して実行できます。

USE [test]
GO

DECLARE @site_name VARCHAR(50);

EXEC spNhanvien @site_name OUT;

PRINT @site_name;

GO

このスクリプトでは、まず変数 @site_name を宣言し、EXEC文を使ってプロシージャを実行しています。実行結果は PRINT 文によって画面に出力されます。

DROP PROCEDURE（プロシージャの削除）

プロシージャを作成した後、不要になったり仕様変更が必要になったりして、データベースから削除したいケースもあります。その場合は DROP PROCEDURE ステートメントを使用します。

構文

プロシージャを削除するには、以下の構文を使用します。

DROP PROCEDURE procedure_name;

パラメータの説明：

procedure_name： 削除したいプロシージャの名前

具体例

DROP PROCEDURE spNhanvien;

このコマンドを実行すると、データベースから spNhanvien プロシージャが完全に削除されます。なお、削除したプロシージャは元に戻せないため、実行前に本当に不要かどうかを確認することをおすすめします。