0
# Parameter Types and Stored Procedures
1

2
Groovy SQL provides a comprehensive type-safe parameter system for stored procedure calls, supporting input, output, and input/output parameters with proper JDBC type mapping and automatic result handling.
3

4
## Parameter Interfaces
5

6
### InParameter
7

8
Interface for input parameters with type information:
9

10
```groovy { .api }
11
interface InParameter {
12
    int getType()      // Get JDBC data type constant
13
    Object getValue()  // Get parameter value
14
}
15
```
16

17
### OutParameter
18

19
Interface for output parameters from stored procedures:
20

21
```groovy { .api }
22
interface OutParameter {
23
    int getType()  // Get JDBC data type constant
24
}
25
```
26

27
### InOutParameter
28

29
Interface for bidirectional parameters (both input and output):
30

31
```groovy { .api }
32
interface InOutParameter extends InParameter, OutParameter {
33
    // Inherits getType() from both interfaces
34
    // Inherits getValue() from InParameter
35
}
36
```
37

38
### ResultSetOutParameter
39

40
Interface for result set output parameters:
41

42
```groovy { .api }
43
interface ResultSetOutParameter extends OutParameter {
44
    // Inherits getType() from OutParameter
45
}
46
```
47

48
### ExpandedVariable
49

50
Interface for variables that should be expanded into SQL rather than parameterized:
51

52
```groovy { .api }
53
interface ExpandedVariable {
54
    Object getObject()  // Get the object to expand into SQL
55
}
56
```
57

58
## Parameter Factory Methods
59

60
### Input Parameter Creation
61

62
Create typed input parameters:
63

64
```groovy { .api }
65
static InParameter in(int type, Object value)
66
```
67

68
### Output Parameter Creation
69

70
Create output parameters for stored procedures:
71

72
```groovy { .api }
73
static OutParameter out(int type)
74
```
75

76
### Bidirectional Parameter Creation
77

78
Create input/output parameters:
79

80
```groovy { .api }
81
static InOutParameter inout(InParameter in)
82
```
83

84
### Result Set Parameter Creation
85

86
Create result set output parameters:
87

88
```groovy { .api }
89
static ResultSetOutParameter resultSet(int type)
90
```
91

92
### Variable Expansion
93

94
Create variables for SQL expansion:
95

96
```groovy { .api }
97
static ExpandedVariable expand(Object object)
98
```
99

100
## Predefined Type Constants
101

102
### OutParameter Constants
103

104
Pre-defined output parameter constants for all JDBC types:
105

106
```groovy { .api }
107
static final OutParameter ARRAY
108
static final OutParameter BIGINT
109
static final OutParameter BINARY
110
static final OutParameter BIT
111
static final OutParameter BLOB
112
static final OutParameter BOOLEAN
113
static final OutParameter CHAR
114
static final OutParameter CLOB
115
static final OutParameter DATALINK
116
static final OutParameter DATE
117
static final OutParameter DECIMAL
118
static final OutParameter DISTINCT
119
static final OutParameter DOUBLE
120
static final OutParameter FLOAT
121
static final OutParameter INTEGER
122
static final OutParameter JAVA_OBJECT
123
static final OutParameter LONGVARBINARY
124
static final OutParameter LONGVARCHAR
125
static final OutParameter NULL
126
static final OutParameter NUMERIC
127
static final OutParameter OTHER
128
static final OutParameter REAL
129
static final OutParameter REF
130
static final OutParameter SMALLINT
131
static final OutParameter STRUCT
132
static final OutParameter TIME
133
static final OutParameter TIMESTAMP
134
static final OutParameter TINYINT
135
static final OutParameter VARBINARY
136
static final OutParameter VARCHAR
137
```
138

139
### Typed InParameter Factory Methods
140

141
Create typed input parameters using convenience methods:
142

143
```groovy { .api }
144
static InParameter ARRAY(Object value)
145
static InParameter BIGINT(Object value)
146
static InParameter BINARY(Object value)
147
static InParameter BIT(Object value)
148
static InParameter BLOB(Object value)
149
static InParameter BOOLEAN(Object value)
150
static InParameter CHAR(Object value)
151
static InParameter CLOB(Object value)
152
static InParameter DATALINK(Object value)
153
static InParameter DATE(Object value)
154
static InParameter DECIMAL(Object value)
155
static InParameter DISTINCT(Object value)
156
static InParameter DOUBLE(Object value)
157
static InParameter FLOAT(Object value)
158
static InParameter INTEGER(Object value)
159
static InParameter JAVA_OBJECT(Object value)
160
static InParameter LONGVARBINARY(Object value)
161
static InParameter LONGVARCHAR(Object value)
162
static InParameter NULL(Object value)
163
static InParameter NUMERIC(Object value)
164
static InParameter OTHER(Object value)
165
static InParameter REAL(Object value)
166
static InParameter REF(Object value)
167
static InParameter SMALLINT(Object value)
168
static InParameter STRUCT(Object value)
169
static InParameter TIME(Object value)
170
static InParameter TIMESTAMP(Object value)
171
static InParameter TINYINT(Object value)
172
static InParameter VARBINARY(Object value)
173
static InParameter VARCHAR(Object value)
174
```
175

176
## Stored Procedure Calls
177

178
### Simple Procedure Calls
179

180
Execute stored procedures and return result codes:
181

182
```groovy { .api }
183
int call(String sql) throws Exception
184
int call(String sql, List<Object> params) throws Exception
185
int call(String sql, Object[] params) throws Exception
186
int call(GString gstring) throws Exception
187
```
188

189
### Procedure Calls with Output Processing
190

191
Execute procedures and process output parameters:
192

193
```groovy { .api }
194
void call(String sql, List<Object> params, Closure closure) throws Exception
195
void call(GString gstring, Closure closure) throws Exception
196
```
197

198
The closure receives an Object[] containing all output parameter values.
199

200
### Procedure Calls Returning Rows
201

202
Execute procedures that return result sets:
203

204
```groovy { .api }
205
List<GroovyRowResult> callWithRows(String sql, List<Object> params, Closure closure) throws SQLException
206
List<GroovyRowResult> callWithRows(GString gstring, Closure closure) throws SQLException
207
```
208

209
### Procedure Calls Returning Multiple Result Sets
210

211
Execute procedures that return multiple result sets:
212

213
```groovy { .api }
214
List<List<GroovyRowResult>> callWithAllRows(String sql, List<Object> params, Closure closure) throws SQLException
215
List<List<GroovyRowResult>> callWithAllRows(GString gstring, Closure closure) throws SQLException
216
```
217

218
## Usage Examples
219

220
### Basic Parameter Types
221

222
```groovy
223
import groovy.sql.Sql
224
import static groovy.sql.Sql.*
225

226
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
227

228
// Create typed input parameters
229
def nameParam = VARCHAR("John Doe")
230
def ageParam = INTEGER(30)
231
def salaryParam = DECIMAL(75000.50)
232
def activeParam = BOOLEAN(true)
233

234
// Use in query
235
sql.execute("INSERT INTO employees (name, age, salary, active) VALUES (?, ?, ?, ?)",
236
           [nameParam, ageParam, salaryParam, activeParam])
237
```
238

239
### Simple Stored Procedure Call
240

241
```groovy
242
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")
243

244
// Call procedure with input parameters
245
def empId = 100
246
def newSalary = 85000
247
def result = sql.call("{ call update_salary(?, ?) }", [empId, newSalary])
248
println "Procedure returned: ${result}"
249
```
250

251
### Stored Procedure with Output Parameters
252

253
```groovy
254
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")
255

256
// Call procedure with input and output parameters
257
def empId = 100
258
def params = [
259
    INTEGER(empId),           // Input parameter
260
    out(VARCHAR),             // Output: employee name
261
    out(DECIMAL),             // Output: current salary
262
    out(DATE)                 // Output: hire date
263
]
264

265
sql.call("{ call get_employee_info(?, ?, ?, ?) }", params) { outParams ->
266
    def employeeName = outParams[0]
267
    def currentSalary = outParams[1] 
268
    def hireDate = outParams[2]
269
    
270
    println "Employee: ${employeeName}"
271
    println "Salary: \$${currentSalary}"
272
    println "Hired: ${hireDate}"
273
}
274
```
275

276
### Input/Output Parameters
277

278
```groovy
279
def sql = Sql.newInstance("jdbc:sqlserver://localhost:1433;databaseName=test", "user", "pass", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
280

281
// Create input parameter that will also receive output
282
def salaryParam = DECIMAL(75000)
283
def inOutSalary = inout(salaryParam)
284

285
def params = [
286
    INTEGER(100),             // Employee ID (input)
287
    DECIMAL(0.10),           // Raise percentage (input)
288
    inOutSalary              // Current salary (input/output)
289
]
290

291
sql.call("{ call give_raise(?, ?, ?) }", params) { outParams ->
292
    def newSalary = outParams[0]  // The updated salary
293
    println "New salary: \$${newSalary}"
294
}
295
```
296

297
### Procedure Returning Result Set
298

299
```groovy
300
def sql = Sql.newInstance("jdbc:postgresql://localhost:5432/test", "user", "pass", "org.postgresql.Driver")
301

302
// Call procedure that returns a result set
303
def deptId = 10
304
def employees = sql.callWithRows("{ call get_department_employees(?) }", [INTEGER(deptId)]) { outParams ->
305
    // Process any output parameters if present
306
    if (outParams) {
307
        println "Additional output: ${outParams}"
308
    }
309
}
310

311
employees.each { emp ->
312
    println "Employee: ${emp.name}, Salary: ${emp.salary}"
313
}
314
```
315

316
### Multiple Result Sets
317

318
```groovy
319
def sql = Sql.newInstance("jdbc:sqlserver://localhost:1433;databaseName=test", "user", "pass", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
320

321
// Call procedure that returns multiple result sets
322
def allResults = sql.callWithAllRows("{ call get_company_report() }", []) { outParams ->
323
    if (outParams) {
324
        println "Report generated at: ${outParams[0]}"
325
    }
326
}
327

328
// Process multiple result sets
329
allResults.eachWithIndex { resultSet, index ->
330
    println "Result Set ${index + 1}:"
331
    resultSet.each { row ->
332
        println "  ${row}"
333
    }
334
}
335
```
336

337
### Complex Parameter Scenarios
338

339
```groovy
340
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")
341

342
// Mix of different parameter types
343
def params = [
344
    VARCHAR("Engineering"),        // Department name
345
    out(INTEGER),                 // Output: department ID
346
    out(DECIMAL),                 // Output: average salary
347
    out(INTEGER),                 // Output: employee count
348
    resultSet(OTHER)              // Output: result set cursor
349
]
350

351
sql.call("{ call analyze_department(?, ?, ?, ?, ?) }", params) { outParams ->
352
    def deptId = outParams[0]
353
    def avgSalary = outParams[1]
354
    def empCount = outParams[2]
355
    def resultSetCursor = outParams[3]
356
    
357
    println "Department ID: ${deptId}"
358
    println "Average Salary: \$${avgSalary}"
359
    println "Employee Count: ${empCount}"
360
    
361
    // Process result set if available
362
    if (resultSetCursor instanceof ResultSet) {
363
        while (resultSetCursor.next()) {
364
            println "Detail: ${resultSetCursor.getString(1)}"
365
        }
366
    }
367
}
368
```
369

370
### Using GString with Parameters
371

372
```groovy
373
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
374

375
def employeeId = 100
376
def raisePercentage = 0.15
377

378
// GString call with parameter
379
sql.call("{ call give_raise(${INTEGER(employeeId)}, ${DECIMAL(raisePercentage)}, ${out(DECIMAL)}) }") { outParams ->
380
    def newSalary = outParams[0]
381
    println "Employee ${employeeId} new salary: \$${newSalary}"
382
}
383
```
384

385
### Variable Expansion
386

387
```groovy
388
def sql = Sql.newInstance("jdbc:h2:mem:test", "sa", "", "org.h2.Driver")
389

390
// Use expand() for dynamic SQL parts that shouldn't be parameterized
391
def tableName = "employees"
392
def columnName = "salary"
393

394
def params = [
395
    expand(tableName),     // Will be expanded directly into SQL
396
    expand(columnName),    // Will be expanded directly into SQL  
397
    DECIMAL(50000)         // Will be parameterized normally
398
]
399

400
// This allows dynamic table/column names while still using parameters for values
401
sql.eachRow("SELECT * FROM ${params[0]} WHERE ${params[1]} > ?", [params[2]]) { row ->
402
    println "Employee: ${row.name}, Salary: ${row.salary}"
403
}
404
```
405

406
### Error Handling with Procedures
407

408
```groovy
409
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:xe", "user", "pass", "oracle.jdbc.OracleDriver")
410

411
try {
412
    def params = [
413
        INTEGER(999),          // Non-existent employee ID
414
        out(VARCHAR),          // Error message output
415
        out(INTEGER)           // Error code output
416
    ]
417
    
418
    sql.call("{ call safe_update_employee(?, ?, ?) }", params) { outParams ->
419
        def errorMessage = outParams[0]
420
        def errorCode = outParams[1]
421
        
422
        if (errorCode != 0) {
423
            println "Procedure error ${errorCode}: ${errorMessage}"
424
        } else {
425
            println "Update successful"
426
        }
427
    }
428
} catch (SQLException e) {
429
    println "SQL Error: ${e.message}"
430
}
431
```