Зачем нужны хранимые процедуры?sql-23

CREATE PROCEDURE GetOrderDetails(@OrderID INT)
AS
BEGIN
    -- Все данные получаются за один вызов
    SELECT o.*, c.Name, c.Email
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE o.OrderID = @OrderID;

    SELECT * FROM OrderItems WHERE OrderID = @OrderID;
END;

CREATE PROCEDURE UpdateCustomerEmail
    @CustomerID INT,
    @NewEmail VARCHAR(255)
AS
BEGIN
    -- Безопасное обновление через параметры
    UPDATE Customers
    SET Email = @NewEmail
    WHERE CustomerID = @CustomerID;
END;

CREATE PROCEDURE ProcessOrderPayment
    @OrderID INT,
    @Amount DECIMAL(18,2),
    @PaymentMethod VARCHAR(50)
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. Проверка наличия заказа
        IF NOT EXISTS (SELECT 1 FROM Orders WHERE OrderID = @OrderID)
            THROW 50001, 'Order not found', 1;

        -- 2. Проверка суммы
        DECLARE @OrderTotal DECIMAL(18,2);
        SELECT @OrderTotal = TotalAmount FROM Orders WHERE OrderID = @OrderID;

        IF @Amount != @OrderTotal
            THROW 50002, 'Invalid payment amount', 1;

        -- 3. Обновление статуса
        UPDATE Orders
        SET Status = 'Paid',
            PaymentDate = GETDATE(),
            PaymentMethod = @PaymentMethod
        WHERE OrderID = @OrderID;

        -- 4. Логирование
        INSERT INTO PaymentLogs(OrderID, Amount, PaymentDate)
        VALUES (@OrderID, @Amount, GETDATE());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

CREATE PROCEDURE GenerateMonthlyReport(@Month INT, @Year INT)
AS
BEGIN
    -- Создание временной таблицы
    CREATE TABLE #ReportData (
        ProductID INT,
        ProductName VARCHAR(100),
        TotalSold INT,
        TotalRevenue DECIMAL(18,2)
    );

    -- Заполнение данными
    INSERT INTO #ReportData
    SELECT p.ProductID, p.ProductName,
           SUM(oi.Quantity), SUM(oi.Quantity * oi.UnitPrice)
    FROM OrderItems oi
    JOIN Orders o ON oi.OrderID = o.OrderID
    JOIN Products p ON oi.ProductID = p.ProductID
    WHERE MONTH(o.OrderDate) = @Month AND YEAR(o.OrderDate) = @Year
    GROUP BY p.ProductID, p.ProductName;

    -- Дополнительные вычисления
    UPDATE #ReportData
    SET TotalRevenue = TotalRevenue * 0.9; -- Учет скидки

    -- Возврат результата
    SELECT * FROM #ReportData ORDER BY TotalRevenue DESC;

    DROP TABLE #ReportData;
END;

CREATE PROCEDURE TransferInventory
    @FromWarehouse INT,
    @ToWarehouse INT,
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Проверка доступного количества
        DECLARE @Available INT;
        SELECT @Available = Quantity
        FROM Inventory
        WHERE WarehouseID = @FromWarehouse AND ProductID = @ProductID;

        IF @Available < @Quantity
            THROW 50003, 'Not enough inventory', 1;

        -- Списание
        UPDATE Inventory
        SET Quantity = Quantity - @Quantity
        WHERE WarehouseID = @FromWarehouse AND ProductID = @ProductID;

        -- Зачисление (или вставка если записи нет)
        IF EXISTS (SELECT 1 FROM Inventory
                  WHERE WarehouseID = @ToWarehouse AND ProductID = @ProductID)
        BEGIN
            UPDATE Inventory
            SET Quantity = Quantity + @Quantity
            WHERE WarehouseID = @ToWarehouse AND ProductID = @ProductID;
        END
        ELSE
        BEGIN
            INSERT INTO Inventory(WarehouseID, ProductID, Quantity)
            VALUES (@ToWarehouse, @ProductID, @Quantity);
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;