Parameters

SQL*XL home

You can use parameters in your SQL. Using parameters you can easially create reusable statements that prompt for values or take values from the worksheet.

There are two types of parameters or variables:

• Bind variables
  These type of variables are true bound variables to the SQL statement. The variables have a data type (e.g. number, text, date) and a direction (in, out or in/out).
  Examples:
  select * from emp where empno = :myempno;
  select * from emp where hiredate = :myhiredate;
  
• Substitution variables
  These variables are string replaced into the SQL text. Therefore the data type of these variables is always text and the direction is always in.
  Example:
  select * from emp where empno = &myempno;
  select * from emp where hiredate = to_date( &myhiredate , 'dd-mon-yyyy');

Note that you can use the following commands to define and prompt for substitution variables:

• define: define a default value for a substitution variable. See the Define command
• accept: prompts for a value for a substitution variable. See the Accept command

More examples:

select * from emp where empno = :A15;

accept myempno prompt 'Enter an employee number:';
select * from emp where empno = &myempno;

define mydeptno = 20;
accept mydeptno prompt 'Enter a department number:';
select * from emp where deptno = &deptno;

select * from emp where ename = :Sheet2!D12;

select * from emp where ename = '&myename';

See also:

• Define command
• Accept command
• Parameters dialog
• Resultset Options dialog
• Techniques
• Dialogs
• Menu and Toolbar Items
• SQL*XL Home