How to Create a Dynamic Logging Function Inside a Stored Procedure

Goal: Dynamically log the before-update state of any row in any table, as JSON, inside your Store_Log table using stored procedures and dynamic SQL in SQL.

Contents hide

1 🧠 Why This Matters

2 ✅ What You’ll Build

3 🔧 Step-by-Step Guide

3.1 ✅ Step 1: Create a Helper Procedure – Get a Row as JSON

3.2 ✅ Step 2: Use It Inside an Update Procedure

4 🧪 Test It

5 ✅ Summary

6 💡 Want to Take It Further?

---

🧠 Why This Matters

Sometimes you need a way to track changes to critical data — who changed what, and what the data looked like before the change.

Instead of hard-coding every log, let’s build a universal, reusable solution using dynamic SQL that:

• Works on any table
• Logs data before updates
• Stores logs as JSON
• Is triggered inside a stored procedure

This is a minimal dynamic audit log — no triggers, no plugins, just native SQL.

---

✅ What You’ll Build

A dynamic logging solution that does the following:

1. Fetches the row before it’s updated — dynamically
2. Converts the row to JSON
3. Stores that JSON in a Store_Log table

---

🔧 Step-by-Step Guide

---

✅ Step 1: Create a Helper Procedure – Get a Row as JSON

We start by creating a stored procedure that:

• Accepts a table name, primary key column, and primary key value
• Uses INFORMATION_SCHEMA.COLUMNS to get all column names
• Dynamically builds a SELECT JSON_OBJECT(...) query
• Returns the JSON object

CREATE PROCEDURE GetRowAsJson (
    IN in_table_name VARCHAR(64),
    IN in_primary_key_name VARCHAR(64),
    IN in_id_value VARCHAR(64),
    OUT out_json_result JSON
)
BEGIN
    DECLARE col_list TEXT DEFAULT '';
    DECLARE finished INT DEFAULT 0;
    DECLARE col_name VARCHAR(64);

    DECLARE col_cursor CURSOR FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = in_table_name
          AND TABLE_SCHEMA = DATABASE();

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN col_cursor;

    read_loop: LOOP
        FETCH col_cursor INTO col_name;
        IF finished = 1 THEN
            LEAVE read_loop;
        END IF;
        SET col_list = CONCAT_WS(', ', col_list, QUOTE(col_name), ', ', col_name);
    END LOOP;

    CLOSE col_cursor;

    SET @dyn_sql = CONCAT(
        'SELECT JSON_OBJECT(', col_list, ') AS json_row ',
        'FROM ', in_table_name, ' WHERE ', in_primary_key_name, ' = ? LIMIT 1'
    );

    PREPARE stmt FROM @dyn_sql;
    SET @id_param = in_id_value;
    EXECUTE stmt USING @id_param;
    DEALLOCATE PREPARE stmt;
END

---

✅ Step 2: Use It Inside an Update Procedure

Now let’s say you have an Orders table, and you want to update an order’s total — but also log what the row looked like before the update.

Here’s how:

CREATE PROCEDURE UpdateOrderDynamicLog (
    IN in_OrderID INT,
    IN in_NewTotal DECIMAL(10,2),
    IN in_User VARCHAR(100)
)
BEGIN
    DECLARE json_before JSON;

    -- Step 1: Get the current row as JSON
    CALL GetRowAsJson('Orders', 'OrderID', in_OrderID, json_before);

    -- Step 2: Perform the update
    UPDATE Orders
    SET Total = in_NewTotal
    WHERE OrderID = in_OrderID;

    -- Step 3: Insert into Store_Log
    INSERT INTO Store_Log (
        StoreTableName,
        StoreTableID,
        MainTableContent,
        CreatedBy
    ) VALUES (
        'Orders',
        in_OrderID,
        json_before,
        in_User
    );
END

---

🧪 Test It

Try running this update and see the log appear:

CALL UpdateOrderDynamicLog(104, 888.88, 'admin_user');

Now check your Store_Log table — you should see something like this stored:

{
  "OrderID": 104,
  "CustomerName": "Alice",
  "Total": 100.50
}

---

✅ Summary

Feature	Implementation
Dynamic SELECT of row	INFORMATION_SCHEMA.COLUMNS + cursor
Convert to JSON	JSON_OBJECT(col1, val1, ...)
Store in log	INSERT into Store_Log
Works for any table?	✅ Yes, fully dynamic

---

💡 Want to Take It Further?

Here are some powerful ideas to extend this approach:

• Log After-Update values for comparison
• Support INSERT and DELETE operations
• Add diff logs to show what changed
• Log the timestamp, IP, or session info
• Add a ChangeType column: 'UPDATE', 'INSERT', 'DELETE'

---