PARAMETERS statement [Interactive SQL]

Specifies parameters to an Interactive SQL script file.

Syntax

PARAMETERS parameter1, parameter2, ...

Remarks

The PARAMETERS statement names the parameters for a script file, so that they can be referenced later in the script file.

Parameters are referenced by putting {parameter1} into the file where you want the named parameter to be substituted. There must be no spaces between the braces and the parameter name.

Interactive SQL prompts for missing parameters when it executes a statement that uses the parameter. The presence of a PARAMETERS statement does not in itself cause prompting for missing parameter values.

If the .SQL file contains a literal string which happens to contain braces, but which do not enclose a parameter name, Interactive SQL does will not prompt you for a value.

If the PARAMETERS statement lists parameters that are not used in the .SQL file, Interactive SQL does not prompt for them and they are not treated as an error.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following Interactive SQL script file takes two parameters.

PARAMETERS department_id, file;
SELECT Surname
FROM GROUPO.Employees
WHERE DepartmentID = {department_id}
>#{file}.dat;

If you save this script in a fictitious file named c:\temp\test.sql, you can run it from Interactive SQL using the following command:

READ 'c:\\temp\\test.sql' [100] [data]

Parameters can appear in literal strings. The following example returns "Hello, World":

-- Hello.sql 
PARAMETERS yourName; 
MESSAGE 'Hello, {yourName}' TO CLIENT;
READ Hello.sql [World]

Running the following statement returns "Hello, World{end}", since Interactive SQL does not prompt you for a parameter called "end":

-- Hello2.sql
PARAMETERS yourName;
MESSAGE 'Hello, {yourName}{end}' TO CLIENT;

The demo database contains a Departments table that contains the ID, name, and other information about a department in a fictitious company. You could write a .SQL file that updated the name of given department, given its ID. Here's what the script might look like:

-- UpdateDepartmentName.sql
PARAMETERS id, name, headID;
UPDATE Departments D SET D.DepartmentName='{name}' where D.DepartmentID={id};

The script is run with a READ statement with two parameters: one for the department ID, and another for the new department name. To change the name of the R&D department (which has an ID of 100) to Research, you would run the following statement:

READ UpdateDepartmentName.sql  [100] [Research]

If the READ statement contains fewer parameters than are declared in the PARAMETERS statement, Interactive SQL prompts for their values the first time they are used in the .SQL file. For example, if you ran the following statement, which omits the new department name, Interactive SQL prompts for the values when it executes the UPDATE statement:

READ UpdateDepartmentName.sql [100];