new Sql(dataSource).call("{call ABC.GetACount($lastName, ${Sql.INTEGER})}") { num ->
  println "Num returned $num"
}

List results = new Sql(dataSource).rows("{call ABC.FindByFirst($firstName)}")

SqlHelper sql = new SqlHelper(dataSource)
List results = sql.callWithRows("{call ABC.FINDBYLAST($lastName, ${Sql.INTEGER}, ${Sql.VARCHAR})}") {
           List<GroovyRowResult> rows, int status, String errorMessage ->
	if (status != 0) {
	  throw new RuntimeException("Error received from stored proc $status : $errorMessage")
	}

	return rows
}

package com.sheetsj.sql

import groovy.sql.GroovyRowResult
import groovy.sql.OutParameter
import groovy.sql.Sql
import groovy.util.logging.Log4j

import java.sql.CallableStatement
import java.sql.Connection
import java.sql.SQLException

import javax.sql.DataSource

/**
 * An extension of groovy Sql class to add a method that can handle
 * stored procedure calls with
 * both output parameters and a ResultSet being returned
 */
@Log4j
class SqlHelper extends Sql {

	SqlHelper(DataSource dataSource) {
		super(dataSource)
	}

	/**
	 * Given a sql with params as GString variables,
	 * will call the given Closure with List of GroovyRowResults similar to Sql.rows()
	 * and the Output variables,
	 * and return the result of the closure
	 * @see http://jira.codehaus.org/browse/GROOVY-3048
	 */
	def callWithRows(GString queryWithParams, Closure closure) {
		log.debug "Calling query $queryWithParams"

		List params = getParameters(queryWithParams)
		String query = asSql(queryWithParams, params)

		Connection conn
		CallableStatement cstmt
		try {
			conn = createConnection()
			cstmt = conn.prepareCall(query);

			setParameters(params, cstmt)
			def hasResultSet = cstmt.execute()

			//Copy resultSet into groovy resultSet rows
			List<GroovyRowResult> rows = []
			if (hasResultSet || cstmt.getMoreResults()) {
				rows = asList(query, cstmt.getResultSet())
			}

			//Set output parameters with rows first
			def outputResults = [rows]
			params.eachWithIndex { param, i ->
				if (param instanceof OutParameter) {
					outputResults << cstmt.getObject(i + 1)
				}
			}

			return closure.call(outputResults.toArray())
		} catch (SQLException e) {
			log.error("Failed to execute $queryWithParams", e)
			throw e
		} finally {
			closeResources(conn, cstmt)
		}
	}
}