ストアド プロシージャを作成する

完了済み

ストアド プロシージャ は、ビジネス ロジックをカプセル化し、アプリケーションのパフォーマンスを向上させる SQL Server の最も強力なツールの 1 つです。 ストアド プロシージャを作成するときは、サーバー上で実行される再利用可能なコード ブロックを作成し、ネットワーク トラフィックを減らし、データ アクセス ロジックを一元化します。

ストアド プロシージャについて

ストアド プロシージャは、SQL Server が 1 つのユニットとして格納および実行する T-SQL ステートメントのコンパイル済みコレクションです。 毎回サーバーに送信する計画外のクエリとは異なり、ストアド プロシージャはプリコンパイルされ、最適化されます。つまり、後続の実行で高速に実行されます。

ストアド プロシージャを使用して、複雑なビジネス ロジックをカプセル化し、データ検証規則を適用し、アプリケーションがデータベースと対話する方法を制御します。 たとえば、テーブルへの直接アクセスを許可する代わりに、データを変更する前に、入力の検証、ビジネス ルールの適用、変更のログ記録を行うストアド プロシージャを作成できます。

パフォーマンスの利点は、クエリ プランのキャッシュから得られます。 計画外のクエリでは、SQL Server は毎回各クエリを解析して最適化する必要があります。 ストアド プロシージャでは、最初の実行後に実行プランがキャッシュされるため、繰り返し操作のオーバーヘッドが軽減されます。

基本的なストアド プロシージャを作成する

ストアド プロシージャの作成は、 CREATE PROCEDURE ステートメントの後に T-SQL ロジックが続きます。 スキーマ修飾識別子を使用してプロシージャ名を指定すると、明確さとパフォーマンスが向上します。

CREATE PROCEDURE dbo.GetCustomerOrders
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    ORDER BY OrderDate DESC;
END

SET NOCOUNT ON ステートメントを使用すると、影響を受ける行数に関するメッセージがクライアントに送信されなくなります。 これにより、ネットワーク トラフィックが減少し、特にプロシージャが複数のステートメントを実行する場合にパフォーマンスが向上します。

プロシージャを作成するときは、 BEGIN キーワードと END キーワードを使用して、プロシージャ本体を明確に定義します。 これにより、コードの読みやすさが高くなり、後でロジックを追加または変更するときにエラーが発生するのを防ぐことができます。

パラメーターを操作する

パラメーターを指定すると、ストアド プロシージャの柔軟性と再利用性が高まります。 呼び出し元アプリケーションの値を受け取る入力パラメーターと、呼び出し元に戻す値を返す出力パラメーターを定義します。

入力パラメーターでは、@ 記号の後にパラメーター名とデータ型が続きます。 パラメーターを省略可能にするには、既定値を指定できます。

CREATE PROCEDURE dbo.GetCustomerOrdersByDate
    @CustomerID int,
    @StartDate datetime = NULL,
    @EndDate datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
        AND (@StartDate IS NULL OR OrderDate >= @StartDate)
        AND (@EndDate IS NULL OR OrderDate <= @EndDate)
    ORDER BY OrderDate DESC;
END

出力パラメーターを使用すると、呼び出し元のアプリケーションに値を返します。 OUTPUT キーワードを使用して定義します。

CREATE PROCEDURE dbo.CalculateOrderTotal
    @OrderID int,
    @TotalAmount decimal(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
    
    RETURN 0;
END

出力パラメーターを指定してプロシージャを呼び出す場合は、変数を宣言して値を受け取り、OUTPUT ステートメントで EXECUTE キーワードを使用する必要があります。

エラー処理を実装する

堅牢なストアド プロシージャには、予期しない状態を管理し、データの整合性を維持するためのエラー処理が含まれます。 TRY...CATCH ブロックを使用してエラー処理を実装します。これは、他のプログラミング言語での例外処理と同様に機能します。

CREATE PROCEDURE dbo.InsertCustomerOrder
    @CustomerID int,
    @OrderDate datetime,
    @TotalAmount decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate customer exists
        IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            RAISERROR('Customer does not exist.', 16, 1);
        END
        
        -- Insert order
        INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
        VALUES (@CustomerID, @OrderDate, @TotalAmount);
        
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN -1;
    END CATCH
END

TRY ブロックにはメイン ロジックが含まれますが、CATCH ブロックは発生したエラーを処理します。 ERROR_MESSAGE()、ERROR_SEVERITY()、ERROR_STATE()などのシステム関数を使用して、エラーの詳細をキャプチャし、呼び出し元のアプリケーションに渡すことができます。

@@TRANCOUNT ブロックでトランザクションをロールバックする前に、常にCATCHを確認してください。 これにより、トランザクションが既に完了しているか、まだ開始されなかった場合にエラーが発生するのを防ぐことができます。

ベスト プラクティスを適用する

ストアド プロシージャを作成するときに確立されたベスト プラクティスに従うと、ストアド プロシージャが保守可能で、安全で、パフォーマンスが高くなります。

スキーマ修飾名を使用する

すべてのオブジェクトにスキーマ修飾名を使用します。 これにより、スキーマ解決のオーバーヘッドを回避することで、あいまいさが解消され、パフォーマンスが向上します。

-- Good
SELECT * FROM dbo.Orders

-- Avoid
SELECT * FROM Orders

パラメーターの検証を実装する

プロシージャの開始時にパラメーター検証を実装します。 不適切なデータを処理するのではなく、入力が無効な場合は高速で失敗します。

IF @CustomerID IS NULL OR @CustomerID <= 0
BEGIN
    RAISERROR('CustomerID must be a positive integer.', 16, 1);
    RETURN -1;
END

SELECT * を避けてください

運用コードでは SELECT * しないでください。 テーブル構造が変更されたときの問題を防ぎ、クエリのパフォーマンスを向上させるために、列を明示的に一覧表示します。

-- Good
SELECT OrderID, CustomerID, OrderDate FROM dbo.Orders

-- Avoid
SELECT * FROM dbo.Orders

わかりやすい名前を使用する

プロシージャの動作を説明するわかりやすい名前を使用します。 操作 (Get、Insert、Update、Delete、Calculate) を示す動詞を含めます。

CREATE PROCEDURE dbo.GetActiveCustomersByRegion
CREATE PROCEDURE dbo.UpdateCustomerAddress
CREATE PROCEDURE dbo.DeleteExpiredOrders

sp_ プレフィックスは使用しないでください

ストアド プロシージャには、 sp_ プレフィックスを使用しないでください。 SQL Server では、このプレフィックスは、 master データベースに格納されているシステム プロシージャ用に予約されます。 プロシージャに sp_ という名前を付けると、SQL Server は、現在のデータベースを確認する前に最初に master を検索し、不要なオーバーヘッドを追加します。

-- Good
CREATE PROCEDURE dbo.GetCustomerOrders

-- Avoid
CREATE PROCEDURE dbo.sp_GetCustomerOrders

これらのプラクティスに基づいて構築すると、運用環境で確実に実行できるように、チームが理解し、維持し、信頼できるストアド プロシージャを作成できます。