Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features

Buy now

Download now

SQL*XL online help

 

SQL*XL: Parameters

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:

SQL*XL ribbon in Excel