ストアドプロシージャでバッチ処理を実行するSQLの実践例

CREATE PROCEDURE ProcedureName
AS
BEGIN
    -- SQL文の集合
END;

CREATE PROCEDURE MonthlySalesSummary
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 対象月の売上データを抽出
    INSERT INTO SalesSummary (ProductID, TotalSales)
    SELECT ProductID, SUM(SalesAmount)
    FROM Sales
    WHERE MONTH(SalesDate) = MONTH(@TargetMonth)
    AND YEAR(SalesDate) = YEAR(@TargetMonth)
    GROUP BY ProductID;
END;

-- 売上データを格納するテーブル
CREATE TABLE Sales (
    SalesID INT PRIMARY KEY,
    SalesDate DATE,
    ProductID INT,
    SalesAmount DECIMAL(10, 2)
);

-- 集計結果を格納するテーブル
CREATE TABLE SalesSummary (
    SummaryID INT PRIMARY KEY IDENTITY,
    ProductID INT,
    TotalSales DECIMAL(10, 2),
    SummaryMonth DATE
);

CREATE PROCEDURE GenerateMonthlySalesSummary
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 対象月の売上データを抽出して集計結果を計算
    INSERT INTO SalesSummary (ProductID, TotalSales, SummaryMonth)
    SELECT ProductID, SUM(SalesAmount), @TargetMonth
    FROM Sales
    WHERE MONTH(SalesDate) = MONTH(@TargetMonth)
    AND YEAR(SalesDate) = YEAR(@TargetMonth)
    GROUP BY ProductID;
END;

-- 2024年4月の売上データを集計
EXEC GenerateMonthlySalesSummary @TargetMonth = '2024-04-01';

CREATE PROCEDURE GenerateMonthlySalesSummary
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 対象月の売上データを抽出して集計結果を計算
        INSERT INTO SalesSummary (ProductID, TotalSales, SummaryMonth)
        SELECT ProductID, SUM(SalesAmount), @TargetMonth
        FROM Sales
        WHERE MONTH(SalesDate) = MONTH(@TargetMonth)
        AND YEAR(SalesDate) = YEAR(@TargetMonth)
        GROUP BY ProductID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- エラーメッセージを返す
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;

BEGIN TRY
    -- エラーチェックが必要なSQL文
END TRY
BEGIN CATCH
    -- エラーハンドリング処理
END CATCH

DECLARE @ErrorMessage NVARCHAR(4000);
SET @ErrorMessage = ERROR_MESSAGE();

CREATE TABLE ErrorLog (
    ErrorLogID INT PRIMARY KEY IDENTITY,
    ErrorMessage NVARCHAR(4000),
    ErrorDateTime DATETIME
);

-- エラーが発生した場合にログに記録
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    SET @ErrorMessage = ERROR_MESSAGE();

    INSERT INTO ErrorLog (ErrorMessage, ErrorDateTime)
    VALUES (@ErrorMessage, GETDATE());

    ROLLBACK TRANSACTION;
    RAISERROR(@ErrorMessage, 16, 1);
END CATCH

CREATE PROCEDURE GenerateMonthlySalesSummary
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 対象月の売上データを抽出して集計結果を計算
        INSERT INTO SalesSummary (ProductID, TotalSales, SummaryMonth)
        SELECT ProductID, SUM(SalesAmount), @TargetMonth
        FROM Sales
        WHERE MONTH(SalesDate) = MONTH(@TargetMonth)
        AND YEAR(SalesDate) = YEAR(@TargetMonth)
        GROUP BY ProductID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        SET @ErrorMessage = ERROR_MESSAGE();

        -- エラーログに記録
        INSERT INTO ErrorLog (ErrorMessage, ErrorDateTime)
        VALUES (@ErrorMessage, GETDATE());

        ROLLBACK TRANSACTION;

        -- エラーメッセージを返す
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;

CREATE INDEX IDX_Sales_SalesDate ON Sales(SalesDate);

CREATE PROCEDURE ProcessSalesDataInBatches
    @BatchSize INT
AS
BEGIN
    DECLARE @BatchID INT = 0;

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION;

        -- バッチ処理
        DELETE TOP (@BatchSize)
        FROM Sales
        WHERE SalesID IN (
            SELECT SalesID
            FROM Sales
            ORDER BY SalesDate
        );

        IF @@ROWCOUNT = 0
        BEGIN
            COMMIT TRANSACTION;
            BREAK;
        END

        COMMIT TRANSACTION;

        SET @BatchID = @BatchID + 1;
    END
END;

-- 非効率なクエリ
SELECT * 
FROM Orders 
WHERE OrderID IN (SELECT OrderID FROM OrderDetails WHERE Quantity > 10);

-- 最適化されたクエリ
SELECT o.*
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE od.Quantity > 10;

-- 購買データを格納するテーブル
CREATE TABLE PurchaseHistory (
    PurchaseID INT PRIMARY KEY,
    CustomerID INT,
    PurchaseDate DATE,
    PurchaseAmount DECIMAL(10, 2)
);

-- 顧客情報を格納するテーブル
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

-- 月次レポートを格納するテーブル
CREATE TABLE MonthlyReport (
    ReportID INT PRIMARY KEY IDENTITY,
    CustomerID INT,
    TotalPurchases DECIMAL(10, 2),
    ReportMonth DATE
);

CREATE PROCEDURE GenerateMonthlyCustomerReport
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 購買履歴を集計し、月次レポートを生成
        INSERT INTO MonthlyReport (CustomerID, TotalPurchases, ReportMonth)
        SELECT c.CustomerID, SUM(ph.PurchaseAmount), @TargetMonth
        FROM Customers c
        JOIN PurchaseHistory ph ON c.CustomerID = ph.CustomerID
        WHERE MONTH(ph.PurchaseDate) = MONTH(@TargetMonth)
        AND YEAR(ph.PurchaseDate) = YEAR(@TargetMonth)
        GROUP BY c.CustomerID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- エラーメッセージを返す
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;

-- 2024年4月の顧客購買履歴を集計
EXEC GenerateMonthlyCustomerReport @TargetMonth = '2024-04-01';

CREATE PROCEDURE GenerateDynamicReport
    @TargetMonth DATE,
    @FieldName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = '
        INSERT INTO MonthlyReport (CustomerID, TotalPurchases, ReportMonth)
        SELECT CustomerID, SUM(' + @FieldName + '), @TargetMonth
        FROM PurchaseHistory
        WHERE MONTH(PurchaseDate) = MONTH(@TargetMonth)
        AND YEAR(PurchaseDate) = YEAR(@TargetMonth)
        GROUP BY CustomerID;
    ';

    EXEC sp_executesql @SQL, N'@TargetMonth DATE', @TargetMonth;
END;

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SET NOCOUNT ON;
    SELECT EmployeeID, EmployeeName, HireDate FROM Employee;
END;

CREATE PROCEDURE GetMonthlySalesTotal
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT SUM(SaleAmount) AS TotalSales
    FROM SalesData
    WHERE MONTH(SaleDate) = MONTH(@TargetMonth)
    AND YEAR(SaleDate) = YEAR(@TargetMonth);
END;

CREATE PROCEDURE GetMonthlySalesTotal
    @TargetMonth DATE
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SELECT SUM(SaleAmount) AS TotalSales
        FROM SalesData
        WHERE MONTH(SaleDate) = MONTH(@TargetMonth)
        AND YEAR(SaleDate) = YEAR(@TargetMonth);
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        SET @ErrorMessage = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
    END CATCH
END;

CREATE PROCEDURE GetDynamicSalesSummary
    @TargetMonth DATE,
    @FieldName NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = '
        SELECT SUM(' + @FieldName + ') AS Total
        FROM ProductSales
        WHERE MONTH(SaleDate) = MONTH(@TargetMonth)
        AND YEAR(SaleDate) = YEAR(@TargetMonth);
    ';

    EXEC sp_executesql @SQL, N'@TargetMonth DATE', @TargetMonth;
END;