Using host variables in SQL statements

When your program retrieves data, the values are put into data items that are defined by your program and that are specified with the INTO clause of a SELECT INTO or FETCH statement. The data items are called host variables.

A host variable is a field in your program that is specified in an SQL statement, usually as the source or target for the value of a column. The host variable and column must have compatible data types. Host variables cannot be used to identify SQL objects, such as tables or views, except in the DESCRIBE TABLE statement.

A host structure is a group of host variables used as the source or target for a set of selected values (for example, the set of values for the columns of a row). A host structure array is an array of host structures that is used in the multiple-row FETCH and blocked INSERT statements.

Note: By using a host variable instead of a literal value in an SQL statement, you give the application program the flexibility to process different rows in a table or view.

For example, instead of coding an actual department number in a WHERE clause, you can use a host variable set to the department number you are currently interested in.

Host variables are commonly used in SQL statements in these ways:

In a WHERE clause: You can use a host variable to specify a value in the predicate of a search condition, or to replace a literal value in an expression. For example, if you have defined a field called EMPID that contains an employee number, you can retrieve the name of the employee whose number is 000110 with:
```
  MOVE '000110' TO EMPID.
  EXEC SQL
   SELECT LASTNAME
     INTO :PGM-LASTNAME
     FROM CORPDATA.EMPLOYEE
     WHERE EMPNO = :EMPID
  END-EXEC.
```
As a receiving area for column values (named in an INTO clause): You can use a host variable to specify a program data area that is to contain the column values of a retrieved row. The INTO clause names one or more host variables that you want to contain column values returned by SQL. For example, suppose you are retrieving the EMPNO, LASTNAME, and WORKDEPT column values from rows in the CORPDATA.EMPLOYEE table. You could define a host variable in your program to hold each column, then name the host variables with an INTO clause. For example:
```
  EXEC SQL
   SELECT EMPNO, LASTNAME, WORKDEPT
     INTO :CBLEMPNO, :CBLNAME, :CBLDEPT
     FROM CORPDATA.EMPLOYEE
     WHERE EMPNO = :EMPID
  END-EXEC.
```
In this example, the host variable CBLEMPNO receives the value from EMPNO, CBLNAME receives the value from LASTNAME, and CBLDEPT receives the value from WORKDEPT.
As a value in a SELECT clause: When specifying a list of items in the SELECT clause, you are not restricted to the column names of tables and views. Your program can return a set of column values intermixed with host variable values and literal constants. For example:
```
  MOVE '000220' TO PERSON.
  EXEC SQL
   SELECT "A", LASTNAME, SALARY, :RAISE,
        SALARY + :RAISE
     INTO :PROCESS, :PERSON-NAME, :EMP-SAL,
        :EMP-RAISE, :EMP-TTL
     FROM CORPDATA.EMPLOYEE
     WHERE EMPNO = :PERSON
  END-EXEC.
```
The results are:

PROCESS PERSON-NAME EMP-SAL EMP-RAISE EMP-TTL

A LUTZ 29840 4476 34316
As a value in other clauses of an SQL statement:
- The SET clause in an UPDATE statement
- The VALUES clause in an INSERT statement
- The CALL statement