SQL Serverのストアドプロシージャの作成方法：基本構文と活用のポイント

SQL Serverには「ストアドプロシージャ」という非常に便利な機能があります。ストアドプロシージャを利用することで、繰り返し実行するSQLクエリを効率化し、アプリケーションのパフォーマンスを向上させることができます。

本記事では、ストアドプロシージャの基本構文である CREATE PROCEDURE を学び、活用する方法について具体例を交えながら解説します。さらに、学んだ内容を確認するための演習問題とその解答例もご紹介します。

ストアドプロシージャとは？
基本構文：CREATE PROCEDURE
パラメータ付きプロシージャ
ストアドプロシージャの変更と削除
活用例：売上データの集計
演習問題
解答例
まとめ

ストアドプロシージャとは？

ストアドプロシージャとは、データベース内に保存される一連のSQL文のことです。

クエリを毎回記述する代わりに、名前を付けてデータベースに保存し、必要に応じて簡単に呼び出せるようになります。

主なメリット

パフォーマンスの向上
ストアドプロシージャはサーバー上でコンパイルされ、再利用可能なため、実行速度が向上します。
セキュリティの強化
パラメータを利用することでSQLインジェクションを防ぎやすくなります。
コードの再利用
同じクエリを何度も利用できるため、メンテナンスが簡単になります。

基本構文：CREATE PROCEDURE

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

CREATE PROCEDURE プロシージャ名
AS
BEGIN
    -- 実行したいSQL文
END

構文の詳細

CREATE PROCEDURE: ストアドプロシージャを作成するキーワード。
プロシージャ名: 任意の名前を指定。
AS: SQL文の開始を示します。
BEGIN と END: 実行するSQL文を囲む。

サンプルコード

以下は、「全ての従業員情報を取得するストアドプロシージャ」を作成する例です。

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees;
END

このコードを実行すると、GetAllEmployees というプロシージャが作成されます。以降は以下のように簡単に呼び出せます。

EXEC GetAllEmployees;

パラメータ付きプロシージャ

パラメータ付きプロシージャとは、引数（パラメータ）を使用して外部から値を渡したり、実行結果を返したりすることができ、柔軟なクエリ実行が可能になります。

パラメータ付き構文

CREATE PROCEDURE プロシージャ名
    @パラメータ名 データ型
AS
BEGIN
    -- パラメータを利用したSQL文
END

サンプルコード

特定の従業員情報を取得するプロシージャを作成してみましょう。

CREATE PROCEDURE GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeId;
END

このプロシージャは、従業員IDを引数として受け取り、そのIDに対応する従業員情報を取得します。

実行例

EXEC GetEmployeeById @EmployeeId = 1;

入力パラメータの使用方法

入力パラメータは、外部からプロシージャに値を渡します。

まずは、入力パラメータの使い方を見ていきましょう。

入力パラメータの定義

以下は、入力パラメータを持つ簡単なストアドプロシージャの例です。

CREATE PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, DepartmentID
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

入力パラメータ付きプロシージャの実行

作成したプロシージャを実行してみましょう。

EXEC GetEmployeeByDepartment @DepartmentID = 2;

上記の例では、DepartmentIDが2の従業員データを取得します。

出力パラメータの使用方法

出力パラメータは、プロシージャ内で計算した結果を外部に返します。

次に、出力パラメータの使い方を解説します。

出力パラメータの定義

出力パラメータを定義するには、OUTPUTキーワードを使用します。

CREATE PROCEDURE GetDepartmentName
    @DepartmentID INT,
    @DepartmentName NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @DepartmentName = DepartmentName
    FROM Departments
    WHERE DepartmentID = @DepartmentID;
END;

出力パラメータ付きプロシージャの実行

出力パラメータを受け取るには、変数を用意します。

DECLARE @DeptName NVARCHAR(50);

EXEC GetDepartmentName 
    @DepartmentID = 2, 
    @DepartmentName = @DeptName OUTPUT;

PRINT @DeptName; -- 出力結果: 「Sales」など

パラメータ付きプロシージャのメリット

再利用性: 一度作成したプロシージャを複数のクエリで再利用可能。
セキュリティ: SQLインジェクション対策に効果的。
保守性: 複雑なクエリを隠蔽し、コードの読みやすさを向上。

ストアドプロシージャの変更と削除

既存のストアドプロシージャを変更する場合は ALTER PROCEDURE を、削除する場合は DROP PROCEDURE を使用します。

プロシージャの変更

ALTER PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END

プロシージャの削除

DROP PROCEDURE GetAllEmployees;

活用例：売上データの集計

ストアドプロシージャを使った活用例として、売上データを月ごとに集計するプロシージャを作成してみます。

サンプルコード

CREATE PROCEDURE GetMonthlySales
    @Year INT,
    @Month INT
AS
BEGIN
    SELECT 
        ProductID,
        SUM(SalesAmount) AS TotalSales
    FROM Sales
    WHERE YEAR(SaleDate) = @Year AND MONTH(SaleDate) = @Month
    GROUP BY ProductID;
END

実行例

EXEC GetMonthlySales @Year = 2023, @Month = 12;

このプロシージャは、指定された年と月の売上データを商品別に集計して表示します。

演習問題

以下の問題に取り組み、理解を深めましょう。

問題1: ストアドプロシージャの作成

以下の条件に基づいてストアドプロシージャを作成してください。

テーブル名：Orders
機能：特定の顧客ID (CustomerId) に基づいて注文情報を取得する。

問題2: 既存のプロシージャの変更

以下の GetAllEmployees プロシージャを変更し、従業員の氏名のみを返すようにしてください。

解答例

問題1の解答

CREATE PROCEDURE GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    SELECT * FROM Orders WHERE CustomerID = @CustomerId;
END

問題2の解答

ALTER PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT FirstName, LastName FROM Employees;
END

まとめ

SQL Serverのストアドプロシージャを活用することで、効率的かつ安全にデータベース操作が可能になります。

本記事では基本構文から応用例、演習問題まで幅広く解説しました。ぜひ実際に手を動かして試してみてください！