Generating SQL Stored Procedure Calls from JSON Parameters

JSON to Stored Procedure Calls

Sometimes instead of raw INSERT/UPDATE statements, you need to call a stored procedure with JSON data as parameters. This pattern is common in enterprise applications where business logic lives in the database.

Example JSON

{
  "procedure": "sp_create_user",
  "params": {
    "p_name": "Alice",
    "p_email": "alice@example.com",
    "p_age": 30,
    "p_department_id": 5,
    "p_active": true
  }
}

PostgreSQL Output

CALL sp_create_user(
  p_name := 'Alice',
  p_email := 'alice@example.com',
  p_age := 30,
  p_department_id := 5,
  p_active := TRUE
);

MySQL Output

CALL sp_create_user(
  'Alice',
  'alice@example.com',
  30,
  5,
  TRUE
);

SQL Server Output

EXEC sp_create_user
  @p_name = 'Alice',
  @p_email = 'alice@example.com',
  @p_age = 30,
  @p_department_id = 5,
  @p_active = 1;

Parameter Styles

Database	Named params	Positional	Prefix
PostgreSQL	param := value	Yes	None
MySQL	Not supported	Yes	None
SQL Server	@param = value	Yes	@
Oracle	param => value	Yes	None

Batch Procedure Calls

For JSON arrays, generate multiple CALL statements:

[
  { "p_name": "Alice", "p_email": "alice@example.com" },
  { "p_name": "Bob", "p_email": "bob@example.com" }
]

CALL sp_create_user(p_name := 'Alice', p_email := 'alice@example.com');
CALL sp_create_user(p_name := 'Bob', p_email := 'bob@example.com');

Function Calls (SELECT)

For SQL functions that return values:

-- PostgreSQL
SELECT * FROM fn_get_user_details(p_user_id := 42);

-- MySQL
SELECT fn_get_user_details(42);

Output Parameters (SQL Server)

DECLARE @result INT;
EXEC sp_create_user
  @p_name = 'Alice',
  @p_email = 'alice@example.com',
  @p_result = @result OUTPUT;
SELECT @result;

When to Use Stored Procedures

Stored procedures are appropriate when business logic, validation, or multi-table operations are encapsulated in the database. The JSON-to-procedure converter bridges the gap between API payloads and database-side logic.