(Scenario) Stored Procedure: Two IN Params, Fetch-Into-Variable, Update, Return Status

-- PostgreSQL: complete scenario procedure
CREATE OR REPLACE PROCEDURE sp_apply_product_rate(
    IN  p_account_id   VARCHAR,
    IN  p_product_code VARCHAR,
    INOUT p_status     VARCHAR DEFAULT 'PENDING'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rate NUMERIC(5,2);
    v_rows_updated INT;
BEGIN
    -- Step 1: Fetch the rate from product_rates into a local variable
    SELECT rate INTO STRICT v_rate
    FROM product_rates
    WHERE product_code = p_product_code
      AND effective_date <= CURRENT_DATE
    ORDER BY effective_date DESC
    LIMIT 1;

    -- Step 2: Update the target table with the fetched rate
    UPDATE account_interest
    SET current_rate = v_rate,
        updated_at   = NOW()
    WHERE account_id = p_account_id;

    GET DIAGNOSTICS v_rows_updated = ROW_COUNT;

    IF v_rows_updated = 0 THEN
        p_status := 'FAILED_ACCOUNT_NOT_UPDATED';
        RETURN;
    END IF;

    p_status := 'SUCCESS';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_status := 'FAILED_PRODUCT_NOT_FOUND';
    WHEN TOO_MANY_ROWS THEN
        p_status := 'FAILED_MULTIPLE_RATES';
    WHEN OTHERS THEN
        p_status := 'FAILED_' || SQLERRM;
END;
$$;

-- PostgreSQL PL/pgSQL
SELECT rate INTO STRICT v_rate
FROM product_rates
WHERE product_code = p_product_code;

-- Oracle PL/SQL
SELECT rate INTO v_rate
FROM product_rates
WHERE product_code = p_product_code;

-- SQL Server T-SQL
SELECT @v_rate = rate
FROM product_rates
WHERE product_code = @p_product_code;

-- Alternative T-SQL: SET with subquery
SET @v_rate = (
    SELECT rate FROM product_rates
    WHERE product_code = @p_product_code
);

-- PostgreSQL: STRICT enforces exactly one row
BEGIN
    SELECT rate INTO STRICT v_rate
    FROM product_rates
    WHERE product_code = p_product_code;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'No rate found for product %', p_product_code;
        p_status := 'FAILED_PRODUCT_NOT_FOUND';
    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'Multiple rates for product %', p_product_code;
        p_status := 'FAILED_MULTIPLE_RATES';
END;

-- SQL Server: explicit check after SELECT
SELECT @v_rate = rate
FROM product_rates
WHERE product_code = @p_product_code;

IF @v_rate IS NULL
BEGIN
    SET @p_status = 'FAILED_PRODUCT_NOT_FOUND';
    RETURN;
END;

-- PostgreSQL: GET DIAGNOSTICS
UPDATE account_interest
SET current_rate = v_rate
WHERE account_id = p_account_id;

GET DIAGNOSTICS v_rows_updated = ROW_COUNT;

IF v_rows_updated = 0 THEN
    p_status := 'FAILED_ACCOUNT_NOT_UPDATED';
    RETURN;
END IF;

-- Oracle: SQL%ROWCOUNT
UPDATE account_interest
SET current_rate = v_rate
WHERE account_id = p_account_id;

IF SQL%ROWCOUNT = 0 THEN
    p_status := 'FAILED_ACCOUNT_NOT_UPDATED';
    ROLLBACK;
    RETURN;
END IF;

-- SQL Server: @@ROWCOUNT
UPDATE account_interest
SET current_rate = @v_rate
WHERE account_id = @p_account_id;

IF @@ROWCOUNT = 0
BEGIN
    SET @p_status = 'FAILED_ACCOUNT_NOT_UPDATED';
    ROLLBACK TRANSACTION;
    RETURN;
END;

-- SQL Server T-SQL: complete scenario with TRY/CATCH
CREATE PROCEDURE sp_apply_product_rate
    @p_account_id   VARCHAR(20),
    @p_product_code VARCHAR(20),
    @p_status       VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @v_rate DECIMAL(5,2);

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT @v_rate = rate
        FROM product_rates
        WHERE product_code = @p_product_code;

        IF @v_rate IS NULL
        BEGIN
            SET @p_status = 'FAILED_PRODUCT_NOT_FOUND';
            ROLLBACK TRANSACTION;
            RETURN;
        END;

        UPDATE account_interest
        SET current_rate = @v_rate,
            updated_at   = GETDATE()
        WHERE account_id = @p_account_id;

        IF @@ROWCOUNT = 0
        BEGIN
            SET @p_status = 'FAILED_ACCOUNT_NOT_UPDATED';
            ROLLBACK TRANSACTION;
            RETURN;
        END;

        COMMIT TRANSACTION;
        SET @p_status = 'SUCCESS';
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        SET @p_status = 'FAILED_' + ERROR_MESSAGE();
    END CATCH;
END;

package com.bank.service;

import java.sql.*;
import java.util.logging.Logger;

public class RateApplicationService {

    private static final Logger LOG = Logger.getLogger(
        RateApplicationService.class.getName());

    private final Connection connection;

    public RateApplicationService(Connection connection) {
        this.connection = connection;
    }

    public String applyProductRate(String accountId, String productCode)
            throws SQLException {
        String sql = "CALL sp_apply_product_rate(?, ?, NULL)";
        try (CallableStatement cs = connection.prepareCall(sql)) {
            cs.setString(1, accountId);
            cs.setString(2, productCode);
            cs.execute();
            ResultSet rs = cs.getResultSet();
            if (rs.next()) {
                String status = rs.getString(1);
                LOG.info("Rate application status: " + status);
                return status;
            }
            return "UNKNOWN";
        }
    }
}

Output:
Rate application status: SUCCESS

Correctness Checklist:
  1. SELECT ... INTO with STRICT (or explicit NULL check)
  2. Handle NO_DATA_FOUND and TOO_MANY_ROWS
  3. Check @@ROWCOUNT / SQL%ROWCOUNT / GET DIAGNOSTICS after UPDATE
  4. COMMIT on success, ROLLBACK on any failure
  5. Set status on EVERY path (success, no source, no target, exception)
  6. Keep the transaction short to reduce lock contention

-- Senior addition: FOR UPDATE to lock the source row
SELECT rate INTO STRICT v_rate
FROM product_rates
WHERE product_code = p_product_code
FOR UPDATE;
-- Now no other transaction can modify this rate row
-- until we COMMIT or ROLLBACK