Calling a stored procedure

Stored procedure implementation in most SQL database servers is vendor-specific. JDBC offers a generic way for calling those and Groovy's Sql class helps to simplify that task.

This recipe will demonstrate how to utilize the Sql class to make stored procedure calls.

Getting ready

Let's use the same cookbook database, created and populated, like in the Querying an SQL database recipe:

import static DBUtil.*
import groovy.sql.Sql

def server = startServer()
createSchema()
populate()

Let's also assume we have defined a stored procedure with the following structure:

CREATE PROCEDURE INGREDIENT_USAGE(
    OUT INGREDIENTS_RATE INTEGER,
    IN INGREDIENT_NAME VARCHAR(100))
READS SQL DATA
BEGIN ATOMIC
  SELECT COUNT(*)
    INTO INGREDIENTS_RATE
    FROM INGREDIENT
   WHERE NAME LIKE '%' || INGREDIENT_NAME || '%';
END

The INGREDIENT_USAGE procedure declares one IN parameter and one OUT parameter. The input parameter determines what kind of ingredient we are searching for, and...