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 the output parameter returns the number of times that ingredient appears in our recipes.

You can use the same approach we used for other DDL statements in the Creating a database table recipe to append a new stored procedure definition to the database schema.

The following simple steps demonstrate how we can accomplish our recipe's goal:

We have specified two values in the list passed to the call method. The first value is the type of the OUT parameter that we expect to receive, and the second one is the value for the IN parameter that we pass to the procedure. Sql.DOUBLE is a constant (of groovy.sql.OutParameter type) defined in the groovy.sql.Sql class. There is a constant for every standard JDBC type.

OUT parameter values received back from the procedure are passed to the closure given to the call method as a second parameter. Inside that closure, you are free to manipulate received values, for example, by printing them.

If a stored procedure contains several OUT, IN, or even INOUT parameters, then invoking that does not look much more complex. Let's assume we have the following stored procedure signature:

CREATE PROCEDURE INGREDIENT_USAGE2(
    IN COOKBOOK_ID INTEGER,
    OUT INGREDIENTS_RATE INTEGER,
    INOUT INGREDIENT_NAME VARCHAR(100))
READS SQL DATA
BEGIN ATOMIC
    ...
END

Calling that procedure will look as follows:

def params = [1, Sql.INTEGER, Sql.inout(Sql.VARCHAR('sugar'))]

sql.call(
  '{ CALL INGREDIENT_USAGE2(:cookbook_id, :rate, :pattern)}',
  params) { rate, pattern ->
  println rate
  println pattern
}

The difference from our original snippet is that we pass the INOUT parameter with the help of the Sql.inout(Sql.VARCHAR('sugar')) construct and that final closure runs over two output parameters, rate and pattern.