Name
SET Statement
The SET statement assigns a value to a runtime variable. The variables may be platform-specific system variables or user-defined variables.
Platform | Command |
MySQL | Supported |
Oracle | Not supported |
PostgreSQL | Supported |
SQL Server | Supported |
SQL2003 Syntax
SETvariable
=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 a variable must match the datatype of the variable. For example, you cannot assign a string value to a variable that is declared with a numeric datatype. The actual command to create a variable varies from platform to platform. For example, Oracle and SQL Server use the DECLARE statement to declare a variable name and datatype, but other platforms may use other means.
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. Only Oracle uses a consistently different scheme for assigning values to variables. ...
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.