Using Variables in SQL

SQL supports the use of variables. A SQL statement containing variables is considered static. When SQR runs a static statement several times, it runs the same statement, even if the values of the variables change. Because SQL allows variables only in places where literals are allowed (such as in WHERE clauses or INSERT statements), the database can parse the statement before the values for the variables are given.

The ex16a.sqr sample program selects customers from a state that the user specifies:

Program ex16a.sqr
begin-program
   do list_customers_for_state
end-program
begin-procedure list_customers_for_state
input $state maxlen=2 type=char 'Enter state abbreviation'
let $state = upper($state)
begin-select
name (,1)
   position (+1)
from customers
where state = $state
end-select
end-procedure ! list_customers_for_state

Note the use of the $state variable in the select paragraph. When you use a variable in a SQL statement in SQR for PeopleSoft, the SQL statement that is sent to the database contains that variable. SQR binds the variable before the SQL is run. In many cases, the database needs to parse the SQL statement only once. The only item that changes between runs of the select paragraph is the value of the variable. This is the most common example of varying a select paragraph.

In the sample program, the INPUT command prompts the user to enter the value of state. The MAXLEN and TYPE arguments verify the input, ensuring that the user enters a string of no more than two characters. If the entry is incorrect, INPUT reprompts.

The sample program converts the contents of the $state variable to uppercase, which enables the user to enter the state without worrying about the case. In the example, state is uppercase in the database. The sample program shows the LET command that is used with the SQR upper function.

You can let the SQL perform the conversion to uppercase by using where state = upper($state) if you are using an Oracle database or by using where state = ucase($state) if you are using another database. However, SQR enables you to write database-independent code by moving the use of such SQL extensions to the SQR code.

When you run this program, you must specify one of the states that is included in the sample data for the program to return any records. At the prompt, enter IN, MI, NH, NJ, NM, NY, or OH. If you enter NY (the state where most of the customers in the sample data reside), SQR generates the following output:

Output for program ex16a.sqr
John Conway
Eliot Richards
Harriet Bailey
Kate's Out of Date Dress Shop
Corks and Bottles, Inc.