Examples

The example below shows a batch SQL file prompting for a host variable called hDeptName.

SELECT * from dept WHERE dname = :hDeptName;
CREATE TABLE temp 
  SELECT dname, e_no, lname, fname 
  FROM dept, employee 
  WHERE dept.Dept = employee.Dept
  AND dept.Dname = :hDeptName;
SELECT dname, e_no, lname, fname, o_no
  FROM temp, orders
  WHERE temp.e_no = orders.s_no;
UPDATE employee
  SET payrate = payrate * 1.1
  WHERE e_no IN
  (SELECT e_no
  FROM temp);
DROP TABLE temp;

When this script is executed, the user will be prompted to enter a department name, and this value will be is substituted into the query.

The example below shows the same batch SQL file, but this one includes additional batch commands that provide display and error-processing instructions. Notice that there is a semicolon ';' at the end of each command, including the batch commands.

SELECT * from dept WHERE dname = :hDeptName;
NODISPLAY;            --Suppress Result Screen
CREATE TABLE temp 
  SELECT dname, e_no, lname, fname 
  FROM dept, employee 
  WHERE dept.Dept = employee.Dept
  AND dept.Dname = :hDeptName;
SELECT dname, e_no, lname, fname, o_no
  FROM temp, orders
  WHERE temp.e_no = orders.s_no;
DISPLAY;            --Display Result Screen
IF FAIL "No Records Found";  --If no records selected,
IF FAIL EXIT;          --show Message and Exit
UPDATE employee
  SET payrate = payrate * 1.1
  WHERE e_no IN
  (SELECT e_no
  FROM temp);
DROP TABLE temp;