SQL in a Nutshell, 2nd Edition

Name

SET Statement

Synopsis

The SET statement assigns a value to a runtime variable. The variables may be platform-specific system variables or user-defined variables.

Platform	Command
DB2	Supported
MySQL	Supported
Oracle	Not supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

SET variable =value

Keywords

variable

Denotes a system or user-defined variable.

value

Denotes a string or numeric value appropriate to the system or user-defined variable.

Rules at a Glance

Variable values are set for the duration of the session. The value assigned to the variable must match the datatype of the variable. For example, you cannot assign a string value to a variable that is declared as with a numeric datatype. The actual command to create a variable varies from platform to platform. For example, DB2, Oracle, and SQL Server use the DECLARE statement to declare a variable name and datatype, but other platforms may use other means of creating a variable.

The value assigned to a variable does not have to be a literal value. It may be a dynamic value that is derived from a subquery. For example, we assign the maximum employee ID to the emp_id_var variable in the following example:

DECLARE emp_id_var CHAR(5)
SET emp_id_var = (SELECT MAX(emp_id)
   FROM employees WHERE type = 'F')

In this example, an employee type of 'F' indicates that the employee is a full-time, salaried employee.

Programming Tips and Gotchas

The SET statement is easily transportable between the database platforms. ...