Groovy SQL Stored Procedures and Functions with 10+ Examples

how to use Groovy stored procedure calls and functions from Groovy with 10+ examples. Cover input/output parameters, result sets, and database-specific syntax.

“Push logic down to the database when performance demands it.” — Unknown

Joe Celko, SQL for Smarties

Last Updated: March 2026 | Tested on: Groovy 5.x, Java 17+ | Difficulty: Intermediate to Advanced | Reading Time: 17 minutes

Calling a groovy stored procedure through the groovy.sql.Sql class is simple – you get call(), eachRow(), and direct CallableStatement access for output parameters. Stored procedures encapsulate business logic inside the database, improve performance by reducing network round trips, and in many legacy systems, they contain the most critical code in the entire stack.

Groovy makes calling stored procedures simple through the groovy.sql.Sql class. You can use call(), eachRow(), or drop down to CallableStatement when you need output parameters. In this tutorial, we will walk through 10+ practical examples covering input parameters, output parameters, result sets, functions, and database-specific syntax.

If you are new to Groovy SQL, start with Groovy SQL Basics and Groovy SQL CRUD Operations first.

What Are Stored Procedures and Functions?

According to the official Groovy database documentation, stored procedures are precompiled SQL routines stored in the database. Understanding the difference between procedures and functions is important.

FeatureStored ProcedureFunction
ReturnsNothing (or via OUT params)A single value
Called viaCALL procedure(args)SELECT function(args)
Use in SQLCannot be used in SELECTCan be used in SELECT, WHERE
Side effectsCan modify data (INSERT/UPDATE/DELETE)Usually read-only (varies by DB)
Result setsCan return multiple result setsReturns a single value

Key Groovy SQL Methods for Stored Procedures:

  • sql.call() — execute a stored procedure with the CALL syntax
  • sql.eachRow() — works when a procedure returns a result set
  • sql.firstRow() — call functions inline with SELECT
  • connection.prepareCall() — low-level JDBC CallableStatement for output parameters

10 Practical Examples

Example 1: Creating and Calling a Simple Stored Procedure

What we’re doing: Creating a stored procedure in H2 and calling it from Groovy using call().

Example 1: Simple Stored Procedure

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Create a table
sql.execute '''CREATE TABLE employees (id INT AUTO_INCREMENT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice Johnson', 'Engineering', 95000.00)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob Smith', 'Marketing', 72000.00)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie Brown', 'Engineering', 88000.00)"

// Create a stored procedure (H2 syntax -- uses Java/alias approach)
sql.execute '''
    CREATE ALIAS IF NOT EXISTS ADD_EMPLOYEE AS $$
    void addEmployee(java.sql.Connection conn, String name, String dept, double salary) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)"
        );
        ps.setString(1, name);
        ps.setString(2, dept);
        ps.setDouble(3, salary);
        ps.executeUpdate();
        ps.close();
    }
    $$
'''

// Call the stored procedure
sql.call("{CALL ADD_EMPLOYEE('Diana Prince', 'HR', 80000.00)}")
println "Called ADD_EMPLOYEE procedure"

// Call with parameters
sql.call("{CALL ADD_EMPLOYEE(?, ?, ?)}", ['Eve Wilson', 'Engineering', 102000.00])
println "Called ADD_EMPLOYEE with parameters"

// Verify
println "\n=== All Employees ==="
sql.eachRow('SELECT * FROM employees ORDER BY id') {
    println "  #${it.id} ${it.name} | ${it.department} | \$${it.salary}"
}

sql.close()

Output

Called ADD_EMPLOYEE procedure
Called ADD_EMPLOYEE with parameters

=== All Employees ===
  #1 Alice Johnson | Engineering | $95000.00
  #2 Bob Smith | Marketing | $72000.00
  #3 Charlie Brown | Engineering | $88000.00
  #4 Diana Prince | HR | $80000.00
  #5 Eve Wilson | Engineering | $102000.00

What happened here: H2 uses CREATE ALIAS to create stored procedures (they are actually Java methods). The sql.call() method executes a CALL statement. The {CALL procedure_name(args)} syntax is the standard JDBC escape syntax for calling stored procedures and works across all databases.

Example 2: Calling a Function with SELECT

What we’re doing: Creating and calling a database function that returns a value, using it inline in a SELECT statement.

Example 2: Database Functions

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

// Create a function that returns a value
sql.execute '''
    CREATE ALIAS IF NOT EXISTS CALCULATE_TAX AS $$
    double calculateTax(double amount, double rate) {
        return amount * (rate / 100.0);
    }
    $$
'''

// Create a string function
sql.execute '''
    CREATE ALIAS IF NOT EXISTS FORMAT_CURRENCY AS $$
    String formatCurrency(double amount) {
        return String.format("$%,.2f", amount);
    }
    $$
'''

// Create a function that queries data
sql.execute '''CREATE TABLE products (id INT, name VARCHAR(100), price DECIMAL(10,2))'''
sql.execute "INSERT INTO products VALUES (1, 'Laptop', 999.99)"
sql.execute "INSERT INTO products VALUES (2, 'Mouse', 29.99)"
sql.execute "INSERT INTO products VALUES (3, 'Keyboard', 79.99)"

// Call function inline in SELECT
println "=== Tax Calculations ==="
sql.eachRow('SELECT name, price, CALCULATE_TAX(price, 8.5) AS tax FROM products') { row ->
    println "  ${row.name}: price=\$${row.price}, tax=\$${String.format('%.2f', row.tax)}"
}

// Use function with firstRow
def totalTax = sql.firstRow('SELECT SUM(CALCULATE_TAX(price, 8.5)) AS total_tax FROM products')
println "\nTotal tax (8.5%): \$${String.format('%.2f', totalTax.total_tax)}"

// Call function directly
def formatted = sql.firstRow("SELECT FORMAT_CURRENCY(1234567.89) AS result")
println "Formatted: ${formatted.result}"

// Use function in WHERE clause
println "\n=== Products with tax > \$5 ==="
sql.eachRow('SELECT name, price, CALCULATE_TAX(price, 8.5) AS tax FROM products WHERE CALCULATE_TAX(price, 8.5) > 5') { row ->
    println "  ${row.name}: tax=\$${String.format('%.2f', row.tax)}"
}

sql.close()

Output

=== Tax Calculations ===
  Laptop: price=$999.99, tax=$85.00
  Mouse: price=$29.99, tax=$2.55
  Keyboard: price=$79.99, tax=$6.80

Total tax (8.5%): $94.35
Formatted: $1,234,567.89

=== Products with tax > $5 ===
  Laptop: tax=$85.00
  Keyboard: tax=$6.80

What happened here: Functions differ from procedures in that they return a value and can be used directly in SELECT, WHERE, and ORDER BY clauses. This makes them incredibly versatile for calculations, formatting, and data transformation right in your SQL queries. In Groovy, you call functions the same way you would use any SQL expression — through eachRow(), rows(), or firstRow().

Example 3: Procedures with Input Parameters

What we’re doing: Passing multiple input parameters to stored procedures using different parameter binding styles.

Example 3: Input Parameters

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE employees (id INT AUTO_INCREMENT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''

// Procedure to give raises by department
sql.execute '''
    CREATE ALIAS IF NOT EXISTS GIVE_RAISE AS $$
    void giveRaise(java.sql.Connection conn, String dept, double percentage) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement(
            "UPDATE employees SET salary = salary * (1 + ? / 100.0) WHERE department = ?"
        );
        ps.setDouble(1, percentage);
        ps.setString(2, dept);
        int updated = ps.executeUpdate();
        ps.close();
        System.out.println("  Updated " + updated + " employees in " + dept);
    }
    $$
'''

// Insert employees
['Alice:Engineering:90000', 'Bob:Marketing:70000', 'Charlie:Engineering:85000',
 'Diana:HR:75000', 'Eve:Engineering:95000'].each { entry ->
    def (name, dept, sal) = entry.split(':')
    sql.execute "INSERT INTO employees (name, department, salary) VALUES (${name}, ${dept}, ${sal as BigDecimal})"
}

println "=== Before Raises ==="
sql.eachRow('SELECT name, department, salary FROM employees ORDER BY id') {
    println "  ${it.name}: ${it.department} - \$${it.salary}"
}

// Call with positional parameters
println "\n=== Giving 10% Raise to Engineering ==="
sql.call("{CALL GIVE_RAISE(?, ?)}", ['Engineering', 10.0])

// Call with GString parameters
def dept = 'Marketing'
def pct = 5.0
println "\n=== Giving 5% Raise to Marketing ==="
sql.call("{CALL GIVE_RAISE(${dept}, ${pct})}")

println "\n=== After Raises ==="
sql.eachRow('SELECT name, department, salary FROM employees ORDER BY id') {
    println "  ${it.name}: ${it.department} - \$${it.salary}"
}

sql.close()

Output

=== Before Raises ===
  Alice: Engineering - $90000.00
  Bob: Marketing - $70000.00
  Charlie: Engineering - $85000.00
  Diana: HR - $75000.00
  Eve: Engineering - $95000.00

=== Giving 10% Raise to Engineering ===
  Updated 3 employees in Engineering

=== Giving 5% Raise to Marketing ===
  Updated 1 employees in Marketing

=== After Raises ===
  Alice: Engineering - $99000.00
  Bob: Marketing - $73500.00
  Charlie: Engineering - $93500.00
  Diana: HR - $75000.00
  Eve: Engineering - $104500.00

What happened here: The sql.call() method accepts parameters in two forms: a list of positional parameters with ? placeholders, or GString interpolation with ${variables}. Both create parameterized calls under the hood. The procedure modified salary data directly in the database, demonstrating one of the main reasons to use stored procedures — encapsulating business logic at the database level.

Example 4: Output Parameters with CallableStatement

What we’re doing: Using JDBC’s CallableStatement to retrieve output parameter values from stored procedures.

Example 4: Output Parameters

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql
import java.sql.Types

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice', 'Engineering', 95000)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob', 'Marketing', 72000)"
sql.execute "INSERT INTO employees VALUES (3, 'Charlie', 'Engineering', 88000)"
sql.execute "INSERT INTO employees VALUES (4, 'Diana', 'HR', 80000)"

// Create a function that returns a value (H2 uses functions for return values)
sql.execute '''
    CREATE ALIAS IF NOT EXISTS GET_DEPT_COUNT AS $$
    int getDeptCount(java.sql.Connection conn, String dept) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement(
            "SELECT COUNT(*) FROM employees WHERE department = ?"
        );
        ps.setString(1, dept);
        java.sql.ResultSet rs = ps.executeQuery();
        rs.next();
        int count = rs.getInt(1);
        rs.close();
        ps.close();
        return count;
    }
    $$
'''

sql.execute '''
    CREATE ALIAS IF NOT EXISTS GET_DEPT_AVG_SALARY AS $$
    double getDeptAvgSalary(java.sql.Connection conn, String dept) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement(
            "SELECT AVG(salary) FROM employees WHERE department = ?"
        );
        ps.setString(1, dept);
        java.sql.ResultSet rs = ps.executeQuery();
        rs.next();
        double avg = rs.getDouble(1);
        rs.close();
        ps.close();
        return avg;
    }
    $$
'''

// Call function using Groovy SQL call() with output parameter
println "=== Using call() with Output Parameters ==="
sql.call("{? = CALL GET_DEPT_COUNT(?)}", [Sql.INTEGER, 'Engineering']) { count ->
    println "Engineering count: ${count}"
}

sql.call("{? = CALL GET_DEPT_AVG_SALARY(?)}", [Sql.DOUBLE, 'Engineering']) { avgSalary ->
    println "Engineering avg salary: \$${String.format('%.2f', avgSalary)}"
}

// Also works via SELECT for functions
println "\n=== Using SELECT ==="
def result = sql.firstRow("SELECT GET_DEPT_COUNT('Marketing') AS cnt, GET_DEPT_AVG_SALARY('Marketing') AS avg")
println "Marketing: count=${result.cnt}, avg=\$${String.format('%.2f', result.avg)}"

// Iterate through departments
println "\n=== All Departments ==="
['Engineering', 'Marketing', 'HR'].each { dept ->
    sql.call("{? = CALL GET_DEPT_COUNT(?)}", [Sql.INTEGER, dept]) { count ->
        sql.call("{? = CALL GET_DEPT_AVG_SALARY(?)}", [Sql.DOUBLE, dept]) { avg ->
            println "  ${dept}: ${count} employees, avg \$${String.format('%.2f', avg)}"
        }
    }
}

sql.close()

Output

=== Using call() with Output Parameters ===
Engineering count: 2
Engineering avg salary: $91500.00

=== Using SELECT ===
Marketing: count=1, avg=$72000.00

=== All Departments ===
  Engineering: 2 employees, avg $91500.00
  Marketing: 1 employees, avg $72000.00
  HR: 1 employees, avg $80000.00

What happened here: Groovy SQL’s call() method supports output parameters using the {? = CALL function(?)} syntax. The first ? is the return value, and you specify its type using Sql.INTEGER, Sql.DOUBLE, Sql.VARCHAR, etc. The output values are passed to the trailing closure. For simple cases, calling the function via SELECT is often cleaner.

Example 5: Procedures That Return Result Sets

What we’re doing: Calling stored procedures that return query results as result sets and processing them with Groovy closures.

Example 5: Procedures with Result Sets

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE orders (id INT, customer VARCHAR(100), total DECIMAL(10,2), status VARCHAR(20), order_date DATE)'''
sql.execute "INSERT INTO orders VALUES (1, 'Alice', 250.00, 'COMPLETED', '2026-01-15')"
sql.execute "INSERT INTO orders VALUES (2, 'Bob', 150.00, 'PENDING', '2026-02-01')"
sql.execute "INSERT INTO orders VALUES (3, 'Alice', 300.00, 'COMPLETED', '2026-02-15')"
sql.execute "INSERT INTO orders VALUES (4, 'Charlie', 75.00, 'CANCELLED', '2026-02-20')"
sql.execute "INSERT INTO orders VALUES (5, 'Bob', 420.00, 'COMPLETED', '2026-03-01')"

// Create a function that returns a result set (H2 TABLE function)
sql.execute '''
    CREATE ALIAS IF NOT EXISTS GET_ORDERS_BY_STATUS AS $$
    java.sql.ResultSet getOrdersByStatus(java.sql.Connection conn, String status) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement(
            "SELECT id, customer, total, order_date FROM orders WHERE status = ? ORDER BY order_date"
        );
        ps.setString(1, status);
        return ps.executeQuery();
    }
    $$
'''

sql.execute '''
    CREATE ALIAS IF NOT EXISTS GET_CUSTOMER_SUMMARY AS $$
    java.sql.ResultSet getCustomerSummary(java.sql.Connection conn) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement(
            "SELECT customer, COUNT(*) AS order_count, SUM(total) AS total_spent FROM orders GROUP BY customer ORDER BY total_spent DESC"
        );
        return ps.executeQuery();
    }
    $$
'''

// Call and process result set via SELECT
println "=== Completed Orders ==="
sql.eachRow("SELECT * FROM GET_ORDERS_BY_STATUS('COMPLETED')") { row ->
    println "  #${row.id} ${row.customer}: \$${row.total} (${row.order_date})"
}

println "\n=== Pending Orders ==="
sql.eachRow("SELECT * FROM GET_ORDERS_BY_STATUS('PENDING')") { row ->
    println "  #${row.id} ${row.customer}: \$${row.total} (${row.order_date})"
}

// Call with rows() to get a list
println "\n=== Customer Summary ==="
def summary = sql.rows("SELECT * FROM GET_CUSTOMER_SUMMARY()")
summary.each { row ->
    println "  ${row.customer}: ${row.order_count} orders, \$${row.total_spent} total"
}

// Use result in further Groovy processing
def topCustomer = summary.max { it.total_spent }
println "\nTop customer: ${topCustomer.customer} (\$${topCustomer.total_spent})"

sql.close()

Output

=== Completed Orders ===
  #1 Alice: $250.00 (2026-01-15)
  #3 Alice: $300.00 (2026-02-15)
  #5 Bob: $420.00 (2026-03-01)

=== Pending Orders ===
  #2 Bob: $150.00 (2026-02-01)

=== Customer Summary ===
  Bob: 2 orders, $570.00 total
  Alice: 2 orders, $550.00 total
  Charlie: 1 orders, $75.00 total

Top customer: Bob ($570.00)

What happened here: In H2, functions that return result sets can be called with SELECT * FROM function_name(args). This integrates perfectly with Groovy’s eachRow() and rows() methods. The result set is processed exactly like any other query result. Other databases (MySQL, PostgreSQL) have different syntax for returning result sets from procedures, which we will cover in the database-specific section.

Example 6: Error Handling in Stored Procedures

What we’re doing: Handling exceptions thrown by stored procedures and understanding how errors propagate to Groovy.

Example 6: Error Handling

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql
import java.sql.SQLException

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE accounts (id INT PRIMARY KEY, name VARCHAR(50), balance DECIMAL(10,2))'''
sql.execute "INSERT INTO accounts VALUES (1, 'Alice', 1000.00)"
sql.execute "INSERT INTO accounts VALUES (2, 'Bob', 500.00)"

// Create a procedure that validates and throws errors
sql.execute '''
    CREATE ALIAS IF NOT EXISTS TRANSFER_FUNDS AS $$
    void transferFunds(java.sql.Connection conn, int fromId, int toId, double amount) throws java.sql.SQLException {
        if (amount <= 0) {
            throw new java.sql.SQLException("Transfer amount must be positive: " + amount, "45000");
        }

        java.sql.PreparedStatement checkPs = conn.prepareStatement("SELECT balance FROM accounts WHERE id = ?");
        checkPs.setInt(1, fromId);
        java.sql.ResultSet rs = checkPs.executeQuery();

        if (!rs.next()) {
            throw new java.sql.SQLException("Account not found: " + fromId, "45001");
        }

        double balance = rs.getDouble(1);
        rs.close();
        checkPs.close();

        if (balance < amount) {
            throw new java.sql.SQLException("Insufficient funds: balance=" + balance + ", requested=" + amount, "45002");
        }

        java.sql.PreparedStatement debitPs = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");
        debitPs.setDouble(1, amount);
        debitPs.setInt(2, fromId);
        debitPs.executeUpdate();
        debitPs.close();

        java.sql.PreparedStatement creditPs = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?");
        creditPs.setDouble(1, amount);
        creditPs.setInt(2, toId);
        creditPs.executeUpdate();
        creditPs.close();
    }
    $$
'''

// Test case 1: Successful transfer
println "=== Test 1: Valid Transfer ==="
try {
    sql.call("{CALL TRANSFER_FUNDS(?, ?, ?)}", [1, 2, 200.00])
    println "Transfer succeeded"
    sql.eachRow('SELECT * FROM accounts ORDER BY id') { println "  ${it.name}: \$${it.balance}" }
} catch (SQLException e) {
    println "Error: ${e.message} (SQLState: ${e.SQLState})"
}

// Test case 2: Insufficient funds
println "\n=== Test 2: Insufficient Funds ==="
try {
    sql.call("{CALL TRANSFER_FUNDS(?, ?, ?)}", [2, 1, 5000.00])
} catch (SQLException e) {
    println "Error: ${e.message}"
}

// Test case 3: Negative amount
println "\n=== Test 3: Negative Amount ==="
try {
    sql.call("{CALL TRANSFER_FUNDS(?, ?, ?)}", [1, 2, -100.00])
} catch (SQLException e) {
    println "Error: ${e.message}"
}

// Test case 4: Non-existent account
println "\n=== Test 4: Non-existent Account ==="
try {
    sql.call("{CALL TRANSFER_FUNDS(?, ?, ?)}", [99, 1, 100.00])
} catch (SQLException e) {
    println "Error: ${e.message}"
}

// Verify final balances
println "\n=== Final Balances ==="
sql.eachRow('SELECT * FROM accounts ORDER BY id') { println "  ${it.name}: \$${it.balance}" }

sql.close()

Output

=== Test 1: Valid Transfer ===
Transfer succeeded
  Alice: $800.00
  Bob: $700.00

=== Test 2: Insufficient Funds ===
Error: Insufficient funds: balance=700.0, requested=5000.0

=== Test 3: Negative Amount ===
Error: Transfer amount must be positive: -100.0

=== Test 4: Non-existent Account ===
Error: Account not found: 99

=== Final Balances ===
  Alice: $800.00
  Bob: $700.00

What happened here: Exceptions thrown inside stored procedures propagate to Groovy as java.sql.SQLException. You can catch them with standard try-catch and access the SQL state code for programmatic error handling. This is the same pattern used in production systems — the procedure validates business rules and throws meaningful errors that the application layer catches and presents to the user.

Example 7: Calling Built-in Database Functions

What we’re doing: Using built-in database functions (not user-defined) from Groovy for string manipulation, date handling, and math operations.

Example 7: Built-in Database Functions

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE products (id INT, name VARCHAR(100), description VARCHAR(500), price DECIMAL(10,2), created DATE)'''
sql.execute "INSERT INTO products VALUES (1, 'wireless mouse', 'A comfortable wireless mouse for daily use', 29.99, '2025-06-15')"
sql.execute "INSERT INTO products VALUES (2, 'mechanical keyboard', 'Cherry MX switches with RGB lighting', 149.99, '2025-09-01')"
sql.execute "INSERT INTO products VALUES (3, 'USB-C hub', '7-port USB-C dock with HDMI output', 59.99, '2026-01-10')"

// String functions
println "=== String Functions ==="
sql.eachRow('''
    SELECT
        UPPER(name) AS upper_name,
        LENGTH(name) AS name_length,
        CONCAT(UPPER(SUBSTRING(name, 1, 1)), SUBSTRING(name, 2)) AS title_case,
        REPLACE(name, ' ', '-') AS slug
    FROM products ORDER BY id
''') { row ->
    println "  ${row.upper_name} (${row.name_length} chars) -> ${row.title_case} [/${row.slug}/]"
}

// Date functions
println "\n=== Date Functions ==="
sql.eachRow('''
    SELECT
        name,
        created,
        YEAR(created) AS yr,
        MONTH(created) AS mo,
        DATEDIFF('DAY', created, CURRENT_DATE) AS days_ago,
        DATEADD('YEAR', 1, created) AS warranty_expires
    FROM products ORDER BY created
''') { row ->
    println "  ${row.name}: created ${row.created} (${row.days_ago} days ago), warranty until ${row.warranty_expires}"
}

// Math and aggregate functions
println "\n=== Math Functions ==="
def stats = sql.firstRow('''
    SELECT
        COUNT(*) AS total,
        ROUND(AVG(price), 2) AS avg_price,
        ROUND(STDDEV_POP(price), 2) AS std_dev,
        MAX(price) - MIN(price) AS price_range,
        ROUND(SUM(price), 2) AS total_value
    FROM products
''')
println "  Products: ${stats.total}"
println "  Avg price: \$${stats.avg_price}"
println "  Std deviation: \$${stats.std_dev}"
println "  Price range: \$${stats.price_range}"
println "  Total value: \$${stats.total_value}"

// Conditional functions
println "\n=== Conditional Functions ==="
sql.eachRow('''
    SELECT name, price,
        CASE
            WHEN price < 50 THEN 'Budget'
            WHEN price < 100 THEN 'Mid-range'
            ELSE 'Premium'
        END AS tier,
        COALESCE(NULL, name, 'Unknown') AS safe_name
    FROM products ORDER BY price
''') { row ->
    println "  ${row.name}: \$${row.price} [${row.tier}]"
}

sql.close()

Output

=== String Functions ===
  WIRELESS MOUSE (14 chars) -> Wireless mouse [/wireless-mouse/]
  MECHANICAL KEYBOARD (19 chars) -> Mechanical keyboard [/mechanical-keyboard/]
  USB-C HUB (9 chars) -> USB-C hub [/USB-C-hub/]

=== Date Functions ===
  wireless mouse: created 2025-06-15 (266 days ago), warranty until 2026-06-15
  mechanical keyboard: created 2025-09-01 (188 days ago), warranty until 2026-09-01
  USB-C hub: created 2026-01-10 (57 days ago), warranty until 2027-01-10

=== Math Functions ===
  Products: 3
  Avg price: $79.99
  Std deviation: $49.07
  Price range: $120.00
  Total value: $239.97

=== Conditional Functions ===
  wireless mouse: $29.99 [Budget]
  USB-C hub: $59.99 [Mid-range]
  mechanical keyboard: $149.99 [Premium]

What happened here: You do not always need custom stored procedures. Built-in database functions handle string manipulation, date math, aggregation, and conditional logic right in your SQL. These functions run inside the database engine, which is often faster than fetching raw data and processing it in Groovy. The functions shown here (UPPER, LENGTH, DATEDIFF, ROUND, CASE) work on most databases with minor syntax variations.

Example 8: Multiple Output Parameters

What we’re doing: Working with stored procedures that have multiple output parameters.

Example 8: Multiple Output Parameters

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE products (id INT, name VARCHAR(100), price DECIMAL(10,2), quantity INT)'''
sql.execute "INSERT INTO products VALUES (1, 'Widget A', 19.99, 100)"
sql.execute "INSERT INTO products VALUES (2, 'Widget B', 29.99, 50)"
sql.execute "INSERT INTO products VALUES (3, 'Gadget X', 49.99, 75)"
sql.execute "INSERT INTO products VALUES (4, 'Gadget Y', 99.99, 25)"

// H2 functions return a single value, so we simulate multiple outputs
// by returning a pipe-delimited string (common workaround)
sql.execute '''
    CREATE ALIAS IF NOT EXISTS GET_INVENTORY_STATS AS $$
    String getInventoryStats(java.sql.Connection conn) throws java.sql.SQLException {
        java.sql.Statement stmt = conn.createStatement();
        java.sql.ResultSet rs = stmt.executeQuery(
            "SELECT COUNT(*), SUM(quantity), AVG(price), SUM(price * quantity) FROM products"
        );
        rs.next();
        String result = rs.getInt(1) + "|" + rs.getInt(2) + "|" + rs.getDouble(3) + "|" + rs.getDouble(4);
        rs.close();
        stmt.close();
        return result;
    }
    $$
'''

// Call the function
def statsStr = sql.firstRow("SELECT GET_INVENTORY_STATS() AS stats").stats
def (productCount, totalQty, avgPrice, inventoryValue) = statsStr.split('\\|')
println "=== Inventory Statistics ==="
println "  Product count: ${productCount}"
println "  Total quantity: ${totalQty}"
println "  Average price: \$${String.format('%.2f', avgPrice as Double)}"
println "  Inventory value: \$${String.format('%.2f', inventoryValue as Double)}"

// Better approach: return as a result set row
sql.execute '''
    CREATE ALIAS IF NOT EXISTS GET_INVENTORY_REPORT AS $$
    java.sql.ResultSet getInventoryReport(java.sql.Connection conn) throws java.sql.SQLException {
        return conn.createStatement().executeQuery(
            "SELECT COUNT(*) AS product_count, SUM(quantity) AS total_qty, " +
            "ROUND(AVG(price), 2) AS avg_price, SUM(price * quantity) AS inventory_value FROM products"
        );
    }
    $$
'''

// This is cleaner -- one row with named columns
println "\n=== Inventory Report (Clean) ==="
def report = sql.firstRow("SELECT * FROM GET_INVENTORY_REPORT()")
println "  Products: ${report.product_count}"
println "  Total qty: ${report.total_qty}"
println "  Avg price: \$${report.avg_price}"
println "  Value: \$${report.inventory_value}"

sql.close()

Output

=== Inventory Statistics ===
  Product count: 4
  Total quantity: 250
  Average price: $49.99
  Inventory value: $8123.00

=== Inventory Report (Clean) ===
  Products: 4
  Total qty: 250
  Avg price: $49.99
  Value: $8123.00

What happened here: When you need multiple output values from a database function, there are two approaches. The pipe-delimited string approach is a quick workaround that works everywhere. The result set approach (returning a single-row query result) is cleaner because you get named columns. In databases with proper OUT parameter support (MySQL, PostgreSQL, Oracle), you can use actual OUT parameters instead of these workarounds.

Example 9: Stored Procedures with Transactions

What we’re doing: Combining stored procedure calls with Groovy transactions for complex multi-step operations.

Example 9: Procedures with Transactions

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE inventory (product_id INT PRIMARY KEY, name VARCHAR(100), stock INT)'''
sql.execute '''CREATE TABLE orders (id INT AUTO_INCREMENT, product_id INT, quantity INT, status VARCHAR(20))'''

sql.execute "INSERT INTO inventory VALUES (1, 'Laptop', 10)"
sql.execute "INSERT INTO inventory VALUES (2, 'Mouse', 50)"
sql.execute "INSERT INTO inventory VALUES (3, 'Keyboard', 30)"

sql.execute '''
    CREATE ALIAS IF NOT EXISTS CHECK_STOCK AS $$
    int checkStock(java.sql.Connection conn, int productId) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement("SELECT stock FROM inventory WHERE product_id = ?");
        ps.setInt(1, productId);
        java.sql.ResultSet rs = ps.executeQuery();
        if (!rs.next()) throw new java.sql.SQLException("Product not found: " + productId);
        int stock = rs.getInt(1);
        rs.close(); ps.close();
        return stock;
    }
    $$
'''

sql.execute '''
    CREATE ALIAS IF NOT EXISTS REDUCE_STOCK AS $$
    void reduceStock(java.sql.Connection conn, int productId, int qty) throws java.sql.SQLException {
        java.sql.PreparedStatement ps = conn.prepareStatement("UPDATE inventory SET stock = stock - ? WHERE product_id = ?");
        ps.setInt(1, qty);
        ps.setInt(2, productId);
        ps.executeUpdate();
        ps.close();
    }
    $$
'''

// Place an order using procedure calls within a transaction
def placeOrder = { int productId, int quantity ->
    sql.withTransaction {
        // Check stock via procedure
        def stock = sql.firstRow("SELECT CHECK_STOCK(${productId}) AS stock").stock
        if (stock < quantity) {
            throw new RuntimeException("Insufficient stock for product ${productId}: have ${stock}, need ${quantity}")
        }

        // Reduce stock via procedure
        sql.call("{CALL REDUCE_STOCK(?, ?)}", [productId, quantity])

        // Create order record
        def keys = sql.executeInsert('INSERT INTO orders (product_id, quantity, status) VALUES (?, ?, ?)',
            [productId, quantity, 'CONFIRMED'])

        println "  Order #${keys[0][0]} placed: product=${productId}, qty=${quantity}"
    }
}

println "=== Placing Orders ==="
try { placeOrder(1, 3) } catch (e) { println "  FAILED: ${e.message}" }   // OK
try { placeOrder(2, 10) } catch (e) { println "  FAILED: ${e.message}" }  // OK
try { placeOrder(1, 20) } catch (e) { println "  FAILED: ${e.message}" }  // Fail - not enough

println "\n=== Remaining Inventory ==="
sql.eachRow('SELECT * FROM inventory ORDER BY product_id') {
    println "  ${it.name}: ${it.stock} in stock"
}

println "\n=== Orders ==="
sql.eachRow('SELECT * FROM orders ORDER BY id') {
    println "  Order #${it.id}: product=${it.product_id}, qty=${it.quantity} [${it.status}]"
}

sql.close()

Output

=== Placing Orders ===
  Order #1 placed: product=1, qty=3
  Order #2 placed: product=2, qty=10
  FAILED: Insufficient stock for product 1: have 7, need 20

=== Remaining Inventory ===
  Laptop: 7 in stock
  Mouse: 40 in stock
  Keyboard: 30 in stock

=== Orders ===
  Order #1: product=1, qty=3 [CONFIRMED]
  Order #2: product=2, qty=10 [CONFIRMED]

What happened here: This example shows the real-world pattern of combining stored procedures with application-level transactions. The stock check and reduction happen via database procedures, while the order creation and overall transaction management happen in Groovy. The failed order (insufficient stock) triggers a rollback, leaving inventory and orders consistent. This hybrid approach gives you the best of both worlds — database-level logic for performance-critical operations and application-level orchestration for workflow management.

Example 10: Dynamic Procedure Execution

What we’re doing: Building a generic procedure caller that can invoke different procedures dynamically based on configuration or user input.

Example 10: Dynamic Procedure Calls

@Grab('com.h2database:h2:2.2.224')
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:testdb', 'sa', '', 'org.h2.Driver')

sql.execute '''CREATE TABLE audit_log (id INT AUTO_INCREMENT, action VARCHAR(200), performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)'''
sql.execute '''CREATE TABLE employees (id INT, name VARCHAR(100), salary DECIMAL(10,2))'''
sql.execute "INSERT INTO employees VALUES (1, 'Alice', 90000)"
sql.execute "INSERT INTO employees VALUES (2, 'Bob', 70000)"

// Create several utility functions
sql.execute '''
    CREATE ALIAS IF NOT EXISTS CALC_BONUS AS $$
    double calcBonus(double salary, double percentage) {
        return salary * (percentage / 100.0);
    }
    $$
'''

sql.execute '''
    CREATE ALIAS IF NOT EXISTS FORMAT_NAME AS $$
    String formatName(String name, String format) {
        if ("upper".equals(format)) return name.toUpperCase();
        if ("lower".equals(format)) return name.toLowerCase();
        return name;
    }
    $$
'''

// Generic function caller
def callFunction = { String funcName, List params ->
    def placeholders = params.collect { '?' }.join(', ')
    def callSql = "SELECT ${funcName}(${placeholders}) AS result"
    return sql.firstRow(callSql, params)?.result
}

// Use the generic caller
println "=== Dynamic Function Calls ==="

def bonus = callFunction('CALC_BONUS', [90000, 15])
println "Bonus (15% of \$90K): \$${String.format('%.2f', bonus)}"

def upperName = callFunction('FORMAT_NAME', ['Alice Johnson', 'upper'])
println "Upper: ${upperName}"

def lowerName = callFunction('FORMAT_NAME', ['Alice Johnson', 'lower'])
println "Lower: ${lowerName}"

// Configuration-driven function mapping
def functionRegistry = [
    'calculate_bonus':  { Map args -> callFunction('CALC_BONUS', [args.salary, args.percentage]) },
    'format_name':      { Map args -> callFunction('FORMAT_NAME', [args.name, args.format]) }
]

// Execute from configuration
println "\n=== Registry-Based Calls ==="
def tasks = [
    [function: 'calculate_bonus', args: [salary: 85000, percentage: 10]],
    [function: 'format_name', args: [name: 'Bob Smith', format: 'upper']],
    [function: 'calculate_bonus', args: [salary: 95000, percentage: 20]]
]

tasks.each { task ->
    def handler = functionRegistry[task.function]
    if (handler) {
        def result = handler(task.args)
        println "  ${task.function}(${task.args}): ${result}"
    } else {
        println "  Unknown function: ${task.function}"
    }
}

sql.close()

Output

=== Dynamic Function Calls ===
Bonus (15% of $90K): $13500.00
Upper: ALICE JOHNSON
Lower: alice johnson

=== Registry-Based Calls ===
  calculate_bonus([salary:85000, percentage:10]): 8500.0
  format_name([name:Bob Smith, format:upper]): BOB SMITH
  calculate_bonus([salary:95000, percentage:20]): 19000.0

What happened here: The generic callFunction method builds a SELECT statement dynamically to call any database function. The function registry takes this further by mapping logical names to database function calls, which is useful when building plugin systems or configuration-driven applications. The key safety note: never allow untrusted input to specify the function name directly, as that could be a SQL injection vector. Use a whitelist (like the registry map) instead.

Database-Specific Syntax

Stored procedure syntax varies significantly across databases. Here is a quick reference for the most common databases.

Database-Specific Procedure Syntax

import groovy.sql.Sql

// === MySQL ===
// CREATE PROCEDURE get_employee(IN emp_id INT)
// BEGIN
//     SELECT * FROM employees WHERE id = emp_id;
// END

// Groovy call:
// sql.call("{CALL get_employee(?)}", [empId])
// Or for result sets:
// sql.eachRow("CALL get_employee(?)", [empId]) { row -> ... }

// === PostgreSQL ===
// CREATE OR REPLACE FUNCTION get_employee(emp_id INTEGER)
// RETURNS TABLE(id INT, name TEXT, salary NUMERIC) AS $$
// BEGIN
//     RETURN QUERY SELECT e.id, e.name, e.salary FROM employees e WHERE e.id = emp_id;
// END;
// $$ LANGUAGE plpgsql;

// Groovy call:
// sql.eachRow("SELECT * FROM get_employee(?)", [empId]) { row -> ... }

// === Oracle ===
// CREATE OR REPLACE PROCEDURE get_employee(
//     p_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER
// ) AS BEGIN
//     SELECT name, salary INTO p_name, p_salary FROM employees WHERE id = p_id;
// END;

// Groovy call with OUT params:
// sql.call("{CALL get_employee(?, ?, ?)}", [empId, Sql.VARCHAR, Sql.NUMERIC]) { name, salary ->
//     println "${name}: ${salary}"
// }

// === SQL Server ===
// CREATE PROCEDURE GetEmployee @EmpId INT AS
// BEGIN
//     SELECT * FROM Employees WHERE Id = @EmpId
// END

// Groovy call:
// sql.eachRow("{CALL GetEmployee(?)}", [empId]) { row -> ... }

println "Stored procedure syntax varies by database."
println "The JDBC {CALL ...} escape syntax is the most portable."
println "For functions, SELECT function(args) works on most databases."

Output

Stored procedure syntax varies by database.
The JDBC {CALL ...} escape syntax is the most portable.
For functions, SELECT function(args) works on most databases.

Best Practices

DO:

  • Use the JDBC escape syntax {CALL procedure(?)} for maximum portability
  • Use sql.call() for procedures with output parameters
  • Use sql.eachRow() or sql.rows() when calling functions via SELECT
  • Always parameterize procedure arguments — never concatenate user input
  • Wrap procedure calls in transactions when they modify data
  • Handle SQLException to catch procedure-thrown errors

DON’T:

  • Put too much business logic in stored procedures — it becomes hard to test and version control
  • Allow untrusted input to specify procedure names dynamically — use a whitelist
  • Assume stored procedure syntax is the same across databases — always check documentation
  • Forget that H2’s stored procedures use a different syntax (CREATE ALIAS) than MySQL/PostgreSQL

Common Pitfalls

Pitfall 1: Forgetting the JDBC Escape Braces

Pitfall: Missing Escape Braces

// WRONG: Missing the outer braces
// sql.call("CALL my_procedure(?)", [param])
// This may work on some databases but is not portable

// RIGHT: Use JDBC escape syntax with braces
// sql.call("{CALL my_procedure(?)}", [param])

// RIGHT: For functions with return values
// sql.call("{? = CALL my_function(?)}", [Sql.INTEGER, param]) { result -> ... }

println "Always use {CALL ...} or {? = CALL ...} escape syntax!"

Output

Always use {CALL ...} or {? = CALL ...} escape syntax!

Pitfall 2: Output Parameter Type Mismatch

Pitfall: Type Mismatch

// When using output parameters, the type constant must match the procedure's return type

// Available type constants in groovy.sql.Sql:
// Sql.INTEGER, Sql.VARCHAR, Sql.DOUBLE, Sql.FLOAT
// Sql.NUMERIC, Sql.DATE, Sql.TIMESTAMP, Sql.BOOLEAN

// WRONG: Using VARCHAR for a numeric return
// sql.call("{? = CALL get_count()}", [Sql.VARCHAR]) { ... }

// RIGHT: Match the return type
// sql.call("{? = CALL get_count()}", [Sql.INTEGER]) { count -> ... }

println "Match Sql.TYPE to the procedure's actual return type!"

Output

Match Sql.TYPE to the procedure's actual return type!

Conclusion

We covered the key techniques for working with Groovy stored procedures and database functions — from creating and calling simple procedures, to handling input/output parameters, processing result sets, error handling, and database-specific syntax differences.

Stored procedures remain relevant in modern development, especially for performance-critical database operations, legacy system integration, and enforcing business rules at the data layer. Groovy makes calling them clean and simple through the groovy.sql.Sql class.

For the foundation of Groovy database programming, review Groovy SQL Basics. For CRUD patterns, see Groovy SQL CRUD Operations.

Summary

  • Use sql.call("{CALL proc(?)}", params) for stored procedures
  • Use sql.firstRow("SELECT func(?)", params) for functions
  • Output parameters use Sql.INTEGER, Sql.VARCHAR, etc. with a trailing closure
  • Procedure errors propagate as SQLException — always handle them
  • The {CALL ...} JDBC escape syntax is the most portable across databases

If you also work with build tools, CI/CD pipelines, or cloud CLIs, check out Command Playground to practice 105+ CLI tools directly in your browser — no install needed.

Up next: Groovy SQL Update and Delete Operations

Frequently Asked Questions

How do I call a stored procedure in Groovy?

Use sql.call("{CALL procedure_name(?, ?)}", [param1, param2]). The curly braces are the JDBC escape syntax and are required for portability. You can also use GString parameters: sql.call("{CALL procedure_name(${param1}, ${param2})}"). Both create parameterized calls under the hood.

How do I get a return value from a Groovy SQL stored procedure?

For functions that return values, use sql.call("{? = CALL function_name(?)}", [Sql.INTEGER, param]) { result -> println result }. The first ? is the return value, and you specify its type (Sql.INTEGER, Sql.VARCHAR, Sql.DOUBLE, etc.). The result is passed to the trailing closure. Alternatively, call the function via SELECT: sql.firstRow("SELECT function_name(?) AS result", [param]).

What is the difference between sql.call() and sql.execute() for procedures?

sql.call() uses the JDBC CallableStatement interface, which supports output parameters and the standard {CALL …} escape syntax. sql.execute() uses a regular Statement and works for simple procedure calls but does not support output parameters. Use call() when you need output parameters or maximum portability.

Can I call MySQL stored procedures from Groovy?

Yes. MySQL stored procedures work with Groovy SQL using the standard JDBC call syntax: sql.call("{CALL my_procedure(?, ?)}", [param1, param2]). For procedures that return result sets, use sql.eachRow("CALL my_procedure(?)", [param]) { row -> ... }. Make sure to add the MySQL JDBC driver dependency with @Grab('mysql:mysql-connector-java:8.0.33').

How do I handle errors from stored procedures in Groovy?

Errors thrown by stored procedures propagate to Groovy as java.sql.SQLException. Wrap your call in a try-catch block: try { sql.call("{CALL risky_procedure()}") } catch (SQLException e) { println e.message; println e.SQLState }. The SQLState code helps you programmatically identify the error type. Always handle procedure errors in production code.

Previous in Series: Groovy SQL Transactions and Connection Pooling

Next in Series: Groovy SQL Update and Delete Operations

First in Series: Groovy SQL – Connect and Query Databases

Related Topics You Might Like:

This post is part of the Groovy & Grails Cookbook series on TechnoScripts.com

Rahul

GroovyGroovy Database

calldatabasefunctionGroovyJDBCsqlstored procedure

FacebookXPinterestRedditLinkedinWhatsappTelegramEmailShortlink

RahulAuthor posts

Avatar for Rahul

Rahul is a passionate IT professional who loves to sharing his knowledge with others and inspiring them to expand their technical knowledge. Rahul's current objective is to write informative and easy-to-understand articles to help people avoid day-to-day technical issues altogether. Follow Rahul's blog to stay informed on the latest trends in IT and gain insights into how to tackle complex technical issues. Whether you're a beginner or an expert in the field, Rahul's articles are sure to leave you feeling inspired and informed.

Related Posts ...

No comment

Leave a Reply

Your email address will not be published. Required fields are marked *