User Guide > Procedures > Working with SQL Scripts > Designing Parameters for a SQL Script
 
Designing Parameters for a SQL Script
If you want to create a script by first designing its parameters, you must design the parameters in the Parameters Panel and then write a procedure in the SQL Script panel.
Note: If a SQL script has an array as an input parameter, you can supply the input value when executing the script in Studio.
The Design Mode, which you use in this procedure, lets you formulate the input/output parameters for a procedure.
For more on SQL script execution, see Executing a Procedure or Parameterized Query.
To design the parameters for a SQL script in the Parameters panel
1. Create a new SQL script as described in Java Procedures.
2. Select the Parameters tab.
3. Check the Design Mode check box at the top right to make the Parameters panel editable.
Studio displays a Design Mode Notes dialog box.
The Design Mode check box lets you do a top-down design from the Parameters panel.
When you use the Parameters panel to design the interface, the SQL implementation is not automatically updated in the SQL Script panel. You must make sure there is a match between the definitions in the SQL Script panel and the definitions in the Parameters panel using the design mode, including the order in which they are provided; otherwise, the script will generate an error when executed.
4. Review the warning and click OK.
5. Click the Add button to start adding parameters, and select a data type.
When you click Add and select a data type, a new parameter is added with a default name and the specified data type.
Supported data types are:
— Binary—BINARY, BLOB, VARBINARY
— Decimal—DECIMAL, DOUBLE, FLOAT, NUMERIC
— Integer—BIGINT, BIT, INTEGER, SMALLINT, TINYINT
— String—CHAR, CLOB, LONGVARCHAR, VARCHAR
— Time—DATE, TIME, TIMESTAMP
— Complex—CURSOR, XML
The Browse option is for choosing a definition set. See the topic Definition Sets, for details on definition sets.
6. Right-click the parameter name, choose Rename, and type the new name.
7. Optionally, change a parameter’s data type:
a. Right-click the data type name and select Change Type.
b. Right-click the parameter name or data type, and select a data type from the drop-down list.
c. Optionally, specify the length or the number of digits for certain data types.
d. Click OK.
8. Select the parameter, and click Cycle I/O Direction to indicate whether the parameter is input, output, or both.
The Cycle I/O Direction button is not available for parameters that are part of a cursor.
9. Use the navigation buttons to move a parameter up, down, left, or right.
The up and down navigation buttons are enabled when there is more than one parameter.
The left navigation button is enabled for a CURSOR parameter. The right navigation button is enabled for a parameter in a row immediately after the last parameter in a CURSOR.
10. After specifying all of the parameters you want to define, save the edits.
11. In the SQL Script panel, complete the procedure using the parameters designed in the Parameters panel.
12. Save the script.
After the parameters in the design match the parameters in the script on the SQL Script panel, the name of the script is displayed in black.