[SQLServer]ストアドプロシージャの作成_2

1. サンプルテーブルの作成

CREATE_TABLE_Products.sql

CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100),
    Stock INT,
    Price DECIMAL(10, 2)
);

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE OrderDetails (
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

2. サンプルデータの挿入

INSERT_Products.sql

INSERT INTO Products (ProductName, Stock, Price) VALUES ('Product A', 100, 19.99);
INSERT INTO Products (ProductName, Stock, Price) VALUES ('Product B', 200, 29.99);

3. ストアドプロシージャの作成

CREATE_PROCEDURE_usp_CreateOrder.sql

CREATE PROCEDURE usp_CreateOrder
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 在庫チェック
        DECLARE @CurrentStock INT;
        SELECT @CurrentStock = Stock FROM Products WHERE ProductID = @ProductID;

        IF @CurrentStock < @Quantity
        BEGIN
            -- 在庫不足
            RAISERROR ('Not enough stock available', 16, 1);
        END

        -- 注文作成
        INSERT INTO Orders (OrderDate) VALUES (GETDATE());
        DECLARE @OrderID INT = SCOPE_IDENTITY();

        INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
        VALUES (@OrderID, @ProductID, @Quantity);

        -- 在庫更新
        UPDATE Products
        SET Stock = Stock - @Quantity
        WHERE ProductID = @ProductID;

        COMMIT TRANSACTION;
        PRINT 'Order created successfully.';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Error occurred. Transaction rolled back.';
        THROW;
    END CATCH
END;

4. ストアドプロシージャの使用例

EXEC_usp_CreateOrder.sql

-- 正常な注文の例
EXEC usp_CreateOrder @ProductID = 1, @Quantity = 10;

-- 在庫不足の場合の例
EXEC usp_CreateOrder @ProductID = 1, @Quantity = 1000;