SQL Serverでストアドプロシージャを作成・実行する方法

2024-04-02

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

SQL Server や T-SQL などのデータベース環境でよく使われます。

メリット

開発効率の向上: 複雑な処理をまとめて記述することで、コード量を減らし、コードの可読性と保守性を向上できます。
パフォーマンスの向上: データベースサーバー上で実行されるため、クライアント側の負荷を軽減できます。
セキュリティの強化: アクセス権限を制御することで、データの不正アクセスを防ぐことができます。
コードの再利用: 共通処理をストアドプロシージャとして作成することで、コードの再利用性を高めることができます。

デメリット

開発コスト: 作成に時間がかかり、複雑な処理になると設計が難しくなります。
デバッグ: エラーが発生した場合、原因特定が難しくなることがあります。
バージョン管理: バージョン管理が必要となり、運用が複雑になる場合があります。

用途

データの追加、更新、削除
データの検索
複雑な計算処理
データの整合性チェック
アクセス制御

作成方法

SQL Server では、Transact-SQL (T-SQL) を使用してストアドプロシージャを作成できます。

CREATE PROCEDURE [dbo].[GetCustomerOrders]
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID
END

上記は、CustomerID を指定して注文情報を取得するストアドプロシージャの例です。

実行方法

ストアドプロシージャは、EXEC ステートメントを使用して実行できます。

EXEC [dbo].[GetCustomerOrders] @CustomerID = 123

上記は、CustomerID が 123 の注文情報を取得する例です。

ストアドプロシージャは、データベース操作を効率的に行うための強力なツールです。メリットとデメリットを理解した上で、適切な場面で利用することで、開発効率とパフォーマンスを向上させることができます。

顧客情報の取得

CREATE PROCEDURE [dbo].[GetCustomer]
@CustomerID INT
AS
BEGIN
    SELECT *
    FROM Customers
    WHERE CustomerID = @CustomerID
END

注文情報の追加

CREATE PROCEDURE [dbo].[AddOrder]
@CustomerID INT,
@OrderDate DATETIME,
@OrderTotal MONEY
AS
BEGIN
    INSERT INTO Orders
    (CustomerID, OrderDate, OrderTotal)
    VALUES (@CustomerID, @OrderDate, @OrderTotal)
END

このストアドプロシージャは、CustomerID、OrderDate、OrderTotal を指定して注文情報を追加します。

注文情報の更新

CREATE PROCEDURE [dbo].[UpdateOrder]
@OrderID INT,
@OrderDate DATETIME,
@OrderTotal MONEY
AS
BEGIN
    UPDATE Orders
    SET OrderDate = @OrderDate,
        OrderTotal = @OrderTotal
    WHERE OrderID = @OrderID
END

注文情報の削除

CREATE PROCEDURE [dbo].[DeleteOrder]
@OrderID INT
AS
BEGIN
    DELETE FROM Orders
    WHERE OrderID = @OrderID
END

複雑な計算処理

CREATE PROCEDURE [dbo].[GetAverageOrderTotal]
AS
BEGIN
    SELECT AVG(OrderTotal)
    FROM Orders
END

このストアドプロシージャは、注文情報の平均金額を取得します。

データの整合性チェック

CREATE PROCEDURE [dbo].[CheckOrderData]
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE OrderTotal < 0
END

このストアドプロシージャは、注文金額が負の値になっている注文情報がないかチェックします。

アクセス制御

CREATE PROCEDURE [dbo].[GetCustomerOrders]
@CustomerID INT
AS
BEGIN
    IF @CustomerID > 1000
        SELECT *
        FROM Orders
        WHERE CustomerID = @CustomerID
    ELSE
        RAISERROR('アクセス権限がありません', 16, 1)
    END
END

このストアドプロシージャは、CustomerID が 1000 を超える場合のみ注文情報を取得します。

これらのサンプルコードは、ストアドプロシージャの基本的な使用方法を示しています。

ストアドプロシージャは、さまざまな用途で使用できます。詳細については、以下のリソースを参照してください。

これらのデメリットを克服するために、ストアドプロシージャの代替方法として以下の方法が考えられます。

動的 SQL は、文字列変数に SQL ステートメントを格納して実行する方法です。柔軟性がありますが、コードの可読性と保守性が低下する可能性があります。

DECLARE @sql NVARCHAR(MAX)

SET @sql = 'SELECT * FROM Customers WHERE CustomerID = ' + @CustomerID

EXEC (@sql)

ユーザー定義関数は、ストアドプロシージャと同様に、一連の SQL ステートメントをまとめて実行できます。ただし、ストアドプロシージャよりも軽量で、デバッグが容易です。

CREATE FUNCTION [dbo].[GetCustomerName]
(@CustomerID INT)
RETURNS VARCHAR(50)
AS
BEGIN
    SELECT CustomerName
    FROM Customers
    WHERE CustomerID = @CustomerID
END

アプリケーションロジック

複雑な処理の場合は、アプリケーションロジックを記述してデータベース操作を行う方法もあります。開発コストは高くなりますが、可読性と保守性の高いコードを作成することができます。

public string GetCustomerName(int customerId)
{
    using (var connection = new SqlConnection("..."))
    {
        var command = new SqlCommand("SELECT CustomerName FROM Customers WHERE CustomerID = @CustomerID", connection);
        command.Parameters.AddWithValue("@CustomerID", customerId);

        var reader = command.ExecuteReader();
        if (reader.Read())
        {
            return reader.GetString(0);
        }

        return null;
    }
}