Using Dynamic SQL

You may find it too restrictive to use variables only where literals are allowed. In the following example, the ordering of the records changes based on the user’s selection. The program runs the select statement twice. The first time, the first column is called name and the second column is called city, and the program sorts the records by name with a secondary sort by city. The second time, the first column is the city and the second is name, and the program sorts by city with a secondary sort by name. This is the first select paragraph:

select name, city
from customers
order by name, city

This is the second select paragraph:

select city, name
from customers
order by city, name

These statements are different. SQR constructs the statement each time before running it. This technique is called dynamic SQL, and the following sample program illustrates it. To take full advantage of the error-handling procedure, run it with the -CB command-line flag.

Program ex16b.sqr
begin-program
   let $col1 = 'name'
   let $col2 = 'city'
   let #pos = 32
   do list_customers_for_state
   position (+1)
   let $col1 = 'city'
   let $col2 = 'name'
   let #pos = 18
   do list_customers_for_state
end-program
begin-procedure give_warning
   display 'Database error occurred'
   display $sql-error
end-procedure ! give_warning
begin-procedure list_customers_for_state
   let $my_order = $col1 || ',' || $col2
begin-select on-error=give_warning
[$col1] &column1=char (,1)
[$col2] &column2=char (,#pos)
   position (+1)
from customers
order by [$my_order]
end-select
end-procedure ! list_customers_for_state

When you use variables in an SQL statement in SQR to replace literals and more, you make them dynamic variables by enclosing them in square brackets. For example, when you use the [$my_order] dynamic variable in the ORDER BY clause of the select paragraph, SQR places the text from the $my_order variable in that statement. Each time the statement is run, if the text changes a new statement is compiled and run.

Other dynamic variables are [$col1] and [$col2]. They substitute the names of the columns in the select paragraph. The &column1 and &column2 variables are column variables.

You can use dynamic variables to produce reports like this one. The data in the first half of the report is sorted differently from the data in the second half. Also note the give_warning error-handling procedure, discussed next.

The following is the output for Program ex16b.sqr:

John Conway                    New York
Clair Butterfield              Teaneck
Corks and Bottles, Inc.        New York
Eliot Richards                 Queens
Gregory Stonehaven             Everretsville
Harold Alexander Fink          Davenport
Harriet Bailey                 Mamaroneck
Harry's Landmark Diner         Miningville
Isaiah J Schwartz and Company  Zanesville
Jerry's Junkyard Specialties   Frogline
Joe Smith and Company          Big Falls
Kate's Out of Date Dress Shop  New York
Quentin Fields                 Cleveland
Sam Johnson                    Bell Harbor


Bell Harbor      Sam Johnson
Big Falls        Joe Smith and Company
Cleveland        Quentin Fields
Davenport        Harold Alexander Fink	
Everretsville    Gregory Stonehaven
Frogline         Jerry's Junkyard Specialties
Mamaroneck       Harriet Bailey
Miningville      Harry's Landmark Diner
New York         John Conway
New York         Corks and Bottles, Inc.
New York         Kate's Out of Date Dress Shop
Queens           Eliot Richards
Teaneck          Clair Butterfield
Zanesville       Isaiah J Schwartz and Company