Interactive SQL Statements

Interactive SQL statements are SQL statements that ask you for a variable, parameter, or some form of data before fully executing. Say you have a SQL statement that is interactive. The statement is used to create users into a database. The SQL statement could prompt you for information such as user ID, name of user, and phone number. The statement could be for one or many users, and would be executed only once. Otherwise, each user would have to be entered individually with the CREATE USER statement. The SQL statement could also prompt you for privileges. Not all vendors have interactive SQL statements; you must check your particular implementation. The following sections show some examples of interactive SQL using Oracle.

Using Parameters

Parameters are variables that are written in SQL and reside within an application. Parameters can be passed into an SQL statement during runtime, allowing more flexibility for the user executing the statement. Many of the major implementations allow use of these parameters. The following sections show examples of passing parameters for Oracle and Sybase.

Oracle

Parameters in Oracle can be passed into an otherwise static SQL statement.

SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = '&EMP_ID'

The preceding SQL statement returns the EMP_ID, LAST_NAME, and FIRST_NAME for whatever EMP_ID you enter at the prompt.

SELECT *
FROM EMPLOYEE_TBL
WHERE CITY = '&CITY'
AND STATE = '&STATE'

The preceding statement prompts you for the city and the state. The query returns all data for those employees living in the city and state that you entered.

Sybase

Parameters in Sybase can be passed into a stored procedure.

CREATE PROC EMP_SEARCH
(@EMP_ID)
AS
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE_TBL
WHERE EMP_ID = @EMP_ID

Type the following to execute the stored procedure and pass a parameter:

								SP_EMP_SEARCH "443679012"