Chapter 3: Passing parameters as script arguments and Use of variables

SQLS*Plus: Passing parameters as script arguments

You can skip the hints for values that are associated with the substitution of variables by giving them values of parameters in the script, typing the command START / @ command.

The ampersand (&) in the script is placed together with the following figure to replace the substitution variable. At all script executions, the value “&<N>” after @filename will be replaced with the corresponding command-line argument.

Use of variables

& and && indicate substitution variables in SQLS*Plus scripts or commands

When SQLS*Plus encounters a variable defined with &&, it prompts you for the value and then uses this value for every subsequent occurrence of that variable it encounters. The variable and its value are stored.

When you define a variable with &, however, SQLS*Plus discards the variable and its value immediately after use, so that repeated use of &<variablename> results in repeated prompts for the value of <variablename>.

Bind variables

Bind variables are variables created in SQLS*Plus and then used in T-SQL or SQL.

Bind variables can be displayed in SQLS*Plus or referenced in T-SQL subprograms that run in SQLS*Plus.

Creating bind variables

Bind variables created in SQLS*Plus with the VARIABLE command. For example:

VARIABLE v_table_name VARCHAR(50) –s “MY_TABLE”

This command creates a bind variable named v_table_name with a data type of VARCHAR and initial value of “MY_TABLE”.

For more information, see the VARIABLE command. (To list session bind variables, type VARIABLE without arguments.)

Referencing bind variables

Bind variables in T-SQL referenced by typing a colon (:) followed immediately by the name of
the variable. For example

SET @Table_Name = :v_table_name;
0:sa@192.168.1.160\SQLSERVER2008> var name varchar(20) -s "This is a variable"
0:sa@192.168.1.160\SQLSERVER2008> /
begin
DECLARE @Name VARCHAR(20)
SET @Name = :name
print @Name
end

This is a variable

Displaying bind variables

To display the value of a bind variable in SQLS*Plus, use the SQLS*Plus PRINTVAR command. For example:

PRINTVAR name
0:sa@192.168.1.160\SQLSERVER2008> PRINTVAR name
:name
------------------
This is a variable

Setting bind variables values directly

To set the value of a bind variable directly in SQLS*Plus, use the SQLS*Plus SETVAR command. For example:

0:sa@192.168.1.160\SQLSERVER2008> SETVAR name "NEW_ORDERS"
0:sa@192.168.1.160\SQLSERVER2008> PRINTVAR name
:name
----------
NEW_ORDERS

Using bind variables values in non-SQL/TSQL report elements

Bind variable can be used on TTITLE and BTITLE. For example:

0:sa@192.168.1.160\SQLSERVER2008> setvar v3 @@servername;
0:sa@192.168.1.160\SQLSERVER2008>
0:sa@192.168.1.160\SQLSERVER2008> var v2 varchar(10) -s "Title Header"
0:sa@192.168.1.160\SQLSERVER2008>
0:sa@192.168.1.160\SQLSERVER2008> ttitle ':v2 :v3'

0:sa@192.168.1.160\SQLSERVER2008> select top 5 name from sysobjects;

'Title Header ADMIN-PC\SQLSERVER2008
name
--------------------------------------------------------------------------------
sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
0:sa@192.168.1.160\SQLSERVER2008> var

Currently defined bind variables:

var length value
-------------------------------------------------
:v2 10 Title Header
:v3 22 ADMIN-PC\SQLSERVER2008

Assigning SQL Server global variables to bind variables

SQL Server global variable value can be assigned to bind variable duing the time of creation of later using VARIABLE and SETVAR commands. For Example:

VARIABLE v3 varchar(40) –s @@ servername

or

SETVAR v3 @@servername;

Define Variables

Define variables contain either pre-defined value, such as database use or connection string or can be set by user manually or programmatically using COLUMNS and NEW_VALUE option of the columns.

Defining and manually assigning values to define variables

DEFINE Variable = 'value’

Example:

DEFINE LastName = 'Jackson’

Programmatically assigning values to define variables:

1) Define variable <define>

2) Define column with new_value <define>

3) Select data into column from table

Example:

DEFINE LName = 'Jackson’

COLUMN LastName new_value LName

select ‘Olson’ LastName;

Pre-defined variables

_CONNECT_IDENTIFIER
Connection identifier used to make connection.

_CONNECT_DATABASE
Database used to make connection, where available.

_DATE
Current date in default system format

_EDITOR
Editor used by the EDIT command.

_LANGUAGE
Language set in database (as “select @@language”)

_LOGON
Database or OS logon user name used to make connection.

_PRIVILEGE
Privilege level of the current connection (SYSADMIN or not)

_S_EDITION
Database edition of the connected SQL Server Database

_S_VERSION
Version of the connected SQL Server Database.

_S_LEVEL
Level of the connected SQL Server Database.

_USER
Database schema name used to make connection.

Use of define variables in SQLS*Plus command prompt

Define variables can be used to customize SQLS*Plus command prompt.

Example:

0:sa@192.168.1.160\SQLSERVER2008> set sqlprompt
"_USER@_CONNECT_DATABASE>"

sa@AdventureWorksLT2008>set sqlprompt reset

0:sa@192.168.1.160\SQLSERVER2008>

Download SQLS*Plus manual in PDF format