0
# Stored Procedures
1

2
Complete stored procedure support with input/output parameters, result set processing, and CallableStatement wrapper functionality.
3

4
## Capabilities
5

6
### Basic Stored Procedure Calls
7

8
Execute stored procedures with input parameters and retrieve output values.
9

10
```java { .api }
11
// Basic procedure calls
12
public List call(String sql) throws SQLException;
13
public List call(String sql, List<?> params) throws SQLException;
14
public List call(String sql, List<?> params, Closure closure) throws SQLException;
15
public List call(GString gstring) throws SQLException;
16

17
// Map-based parameter calls
18
public List call(String sql, Map params) throws SQLException;
19
public List call(Map params, String sql) throws SQLException;
20
public List call(String sql, Map params, Closure closure) throws SQLException;
21
public List call(Map params, String sql, Closure closure) throws SQLException;
22
```
23

24
**Example**:
25
```groovy
26
import static groovy.sql.Sql.*
27

28
// Simple procedure call with no parameters
29
def result = sql.call('CALL get_server_time()')
30
println "Server time: ${result[0]}"
31

32
// Procedure with input parameters
33
def userId = 123
34
def results = sql.call('CALL get_user_details(?)', [userId])
35
results.each { row ->
36
    println "User data: $row"
37
}
38

39
// Using output parameters
40
def params = [
41
    INTEGER(userId),  // input parameter
42
    out(VARCHAR),     // output parameter for user name
43
    out(INTEGER)      // output parameter for user count
44
]
45
def outputs = sql.call('CALL get_user_info(?, ?, ?)', params)
46
println "User name: ${outputs[0][1]}"  // First row, second column (first output param)
47
println "User count: ${outputs[0][2]}" // First row, third column (second output param)
48
```
49

50
### Result Set Processing
51

52
Process result sets returned by stored procedures with various processing options.
53

54
```java { .api }
55
// Process first result set only
56
public void callWithRows(String sql, Closure closure) throws SQLException;
57
public void callWithRows(String sql, List<?> params, Closure closure) throws SQLException;
58
public void callWithRows(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;
59

60
// Process all result sets
61
public void callWithAllRows(String sql, Closure closure) throws SQLException;
62
public void callWithAllRows(String sql, List<?> params, Closure closure) throws SQLException;
63
public void callWithAllRows(String sql, List<?> params, Closure metaClosure, Closure rowClosure) throws SQLException;
64

65
// Result set processing constants
66
public static final int NO_RESULT_SETS = 0;
67
public static final int FIRST_RESULT_SET = 1;
68
public static final int ALL_RESULT_SETS = 2;
69
```
70

71
**Example**:
72
```groovy
73
// Process first result set from procedure
74
sql.callWithRows('CALL get_active_users()') { row ->
75
    println "Active user: ${row.name} (${row.email})"
76
}
77

78
// Process first result set with parameters
79
sql.callWithRows('CALL get_users_by_department(?)', ['Engineering']) { row ->
80
    println "${row.name} - ${row.title}"
81
}
82

83
// Process all result sets (procedure returns multiple result sets)
84
sql.callWithAllRows('CALL get_user_summary(?)') { resultSet ->
85
    println "Processing result set..."
86
    while (resultSet.next()) {
87
        println "Row: ${resultSet.getString(1)}"
88
    }
89
}
90

91
// With metadata processing
92
sql.callWithRows('CALL get_report_data(?)', [reportId],
93
    { meta ->
94
        println "Report has ${meta.columnCount} columns"
95
        (1..meta.columnCount).each { i ->
96
            println "Column $i: ${meta.getColumnName(i)}"
97
        }
98
    },
99
    { row ->
100
        println "Data row: $row"
101
    }
102
)
103
```
104

105
### Input/Output Parameters
106

107
Handle complex parameter combinations including input, output, and bidirectional parameters.
108

109
```java { .api }
110
// Parameter type interfaces (defined in parameters.md)
111
public interface InParameter {
112
    int getType();
113
    Object getValue();
114
}
115

116
public interface OutParameter {
117
    int getType();
118
}
119

120
public interface InOutParameter extends InParameter, OutParameter {}
121

122
public interface ResultSetOutParameter extends OutParameter {}
123
```
124

125
**Example**:
126
```groovy
127
import static groovy.sql.Sql.*
128
import java.sql.Types
129

130
// Complex parameter scenario
131
def params = [
132
    VARCHAR('john.doe'),        // input: username
133
    out(INTEGER),              // output: user ID
134
    out(VARCHAR),              // output: full name  
135
    out(TIMESTAMP),            // output: last login
136
    inout(VARCHAR('active'))   // input/output: status (input 'active', may be changed by procedure)
137
]
138

139
def results = sql.call('CALL authenticate_user(?, ?, ?, ?, ?)', params)
140

141
// Extract output values from first result row
142
def outputRow = results[0]
143
def userId = outputRow[1]        // First output parameter (user ID)
144
def fullName = outputRow[2]      // Second output parameter (full name)
145
def lastLogin = outputRow[3]     // Third output parameter (last login)
146
def finalStatus = outputRow[4]   // InOut parameter final value
147

148
println "Authentication result:"
149
println "User ID: $userId"
150
println "Name: $fullName" 
151
println "Last login: $lastLogin"
152
println "Status: $finalStatus"
153

154
// Handle null outputs
155
if (userId == null) {
156
    println "Authentication failed"
157
} else {
158
    println "User authenticated successfully"
159
}
160
```
161

162
### ResultSet Output Parameters
163

164
Handle stored procedures that return result sets as output parameters.
165

166
```java { .api }
167
public static ResultSetOutParameter resultSet(int type);
168
```
169

170
**Example**:
171
```groovy
172
import static groovy.sql.Sql.*
173

174
// Procedure with ResultSet output parameter
175
def params = [
176
    INTEGER(departmentId),     // input: department ID
177
    resultSet(Types.OTHER)     // output: result set of employees
178
]
179

180
def results = sql.call('CALL get_department_employees(?, ?)', params)
181

182
// The ResultSet is returned as part of the output
183
def employeeResultSet = results[0][1]  // Second column contains the ResultSet
184

185
// Process the returned ResultSet
186
if (employeeResultSet instanceof ResultSet) {
187
    while (employeeResultSet.next()) {
188
        println "Employee: ${employeeResultSet.getString('name')} - ${employeeResultSet.getString('title')}"
189
    }
190
    employeeResultSet.close()
191
}
192
```
193

194
### Complex Stored Procedure Scenarios
195

196
Handle advanced stored procedure patterns including multiple result sets, cursors, and error handling.
197

198
**Example**:
199
```groovy
200
// Procedure returning multiple result sets
201
sql.call('CALL get_comprehensive_report(?)', [reportId]) { allResults ->
202
    allResults.eachWithIndex { resultSet, index ->
203
        println "Processing result set $index:"
204
        
205
        // Get metadata for this result set
206
        def meta = resultSet.metaData
207
        def columnNames = (1..meta.columnCount).collect { meta.getColumnName(it) }
208
        println "Columns: ${columnNames.join(', ')}"
209
        
210
        // Process rows
211
        while (resultSet.next()) {
212
            def row = columnNames.collectEntries { col ->
213
                [col, resultSet.getObject(col)]
214
            }
215
            println "Row: $row"
216
        }
217
        println "---"
218
    }
219
}
220

221
// Error handling with stored procedures
222
try {
223
    def params = [
224
        INTEGER(accountId),
225
        DECIMAL(amount),
226
        out(VARCHAR),  // output: error message
227
        out(INTEGER)   // output: error code
228
    ]
229
    
230
    def results = sql.call('CALL transfer_funds(?, ?, ?, ?)', params)
231
    
232
    def errorMessage = results[0][2]
233
    def errorCode = results[0][3]
234
    
235
    if (errorCode != 0) {
236
        println "Transfer failed: $errorMessage (code: $errorCode)"
237
    } else {
238
        println "Transfer successful"
239
    }
240
    
241
} catch (SQLException e) {
242
    println "Database error during transfer: ${e.message}"
243
}
244

245
// Working with cursor-based procedures
246
def cursorParams = [
247
    INTEGER(pageSize),
248
    INTEGER(offset),
249
    out(Types.OTHER)  // cursor output
250
]
251

252
sql.call('CALL get_paged_results(?, ?, ?)', cursorParams) { results ->
253
    // Handle cursor-based result processing
254
    // Implementation depends on database-specific cursor handling
255
}
256
```
257

258
### Batch Stored Procedure Calls
259

260
Execute stored procedures in batch mode for improved performance.
261

262
**Example**:
263
```groovy
264
// Batch procedure calls with same parameters structure
265
def userUpdates = [
266
    [userId: 1, status: 'active', lastLogin: new Date()],
267
    [userId: 2, status: 'inactive', lastLogin: new Date() - 30],
268
    [userId: 3, status: 'pending', lastLogin: null]
269
]
270

271
sql.withBatch('CALL update_user_status(?, ?, ?)') { stmt ->
272
    userUpdates.each { update ->
273
        stmt.addBatch([
274
            INTEGER(update.userId),
275
            VARCHAR(update.status),
276
            update.lastLogin ? TIMESTAMP(update.lastLogin) : null
277
        ])
278
    }
279
}
280

281
// Batch with different procedure calls
282
sql.withBatch { stmt ->
283
    // Different procedures in same batch
284
    stmt.addBatch('CALL log_user_action(?, ?)', [INTEGER(userId), VARCHAR('login')])
285
    stmt.addBatch('CALL update_last_seen(?)', [INTEGER(userId)])
286
    stmt.addBatch('CALL increment_login_count(?)', [INTEGER(userId)])
287
}
288
```
289

290
### Database-Specific Stored Procedure Examples
291

292
Handle database-specific stored procedure patterns and syntax variations.
293

294
**Example**:
295
```groovy
296
// MySQL stored procedure with multiple outputs
297
def mysqlParams = [
298
    INTEGER(customerId),
299
    out(DECIMAL),      // total_orders
300
    out(DECIMAL),      // total_amount
301
    out(INTEGER)       // order_count
302
]
303
def mysqlResults = sql.call('CALL get_customer_stats(?, ?, ?, ?)', mysqlParams)
304

305
// Oracle procedure with cursor
306
def oracleParams = [
307
    INTEGER(departmentId),
308
    out(Types.OTHER)   // REF CURSOR
309
]
310
// Oracle-specific cursor handling would go here
311

312
// SQL Server procedure with table-valued parameter
313
def sqlServerData = [
314
    [id: 1, name: 'Item 1', price: 10.99],
315
    [id: 2, name: 'Item 2', price: 15.99]
316
]
317
// SQL Server table-valued parameters require special handling
318

319
// PostgreSQL function call (functions vs procedures)
320
def pgResult = sql.firstRow('SELECT * FROM calculate_interest(?, ?)', [principal, rate])
321
println "Interest: ${pgResult.interest_amount}"
322
```