intro
Explore how the SQL variable mechanism can help you write complex SQL statements in MySQL, PostgreSQL, SQL Server, and Oracle.
Similar to programming, writing complex SQL stored procedures or functions becomes easier when you can store values in local variables. Here is where the SQL variable definition mechanism comes in!
SQL variables can significantly simplify your SQL logic by allowing you to store temporary data in named placeholders. In this article, you will find out what SQL variables are, why they are useful, and how to declare them.
Let’s dive in!
What Is an SQL Variable?
An SQL variable is a placeholder used to store temporary data during the execution of particular SQL statements. Usually, these variables can be used in stored procedures, user-defined SQL functions, dynamic SQL, batches, and scripts.
Just like variables in programming languages, SQL variables are used to store intermediate results, enhance the complexity of procedures, or simplify repetitive tasks. The main benefits of this mechanism are:
Use Cases of SQL Variables
Below are the most common use cases for declaring a variable in SQL:
Declaring a Variable in SQL
Explore how to create and assign SQL variables in the leading database systems. You will also learn when you can define them in each DBMS.
Note that the syntax for declaring and using variables varies between database systems.
MySQL
In MySQL, you can define one or more user-defined variables using the following syntax:
1
SET @var_name_1 = expr_1 [, ..., @var_name_n = expr_n];
The variable name @var_name must start with @ and can include alphanumeric characters, ., _, and $. If you need to use other characters, you may need to quote the variable name as a string or identifier, like @'my-var', @"my-var", or @my-var.
In MySQL, user-defined variables are session-specific, meaning a variable defined by one client cannot be accessed or used by any other client. That also means that these variables are automatically freed when the client session ends.
Here is an example of how you can use them:
1
SET @total = 100;
2
SELECT @total;
MySQL also supports local variables, which are defined using the DECLARE statement within stored procedures and custom SQL functions:
1
DECLARE variable_name datatype [DEFAULT value];
Where:
Local variables are only permitted inside the BEGIN ... END statement and can be assigned values using the SET statement:
1
CREATE PROCEDURE incrementNumber(IN number INT)
2
BEGIN
3
DECLARE count INT DEFAULT number;
4
SET count = count + 1;
5
SELECT count;
6
END;
Local variables are restricted to the procedure or function where they are declared, making them ideal for temporary data within complex SQL logic.
PostgreSQL
Variables in PostgreSQL are only supported in PL/pgSQL, the Postgres procedural language. A PL/pgSQL block is defined through this syntax:
1
[ DECLARE declarations ]
2
BEGIN
3
statements
4
END [ label ];
The declarations section after the DECLARE keyword contains a list of variables, each specified in this way:
1
var_name [ CONSTANT ] var_type [ NOT NULL ] [ DEFAULT = value ];
1
DECLARE @VariableName Type;
Where:
By default, the variable is given the NULL value.
For example, the following DECLARE statement creates a local variable named @mycounter with an INT data type and NULL value:
1
DECLARE @MyCounter INT;
DECLARE can also be used to create multiple local SQL variables at the same time, as in the example below:
1
DECLARE @FirstName NVARCHAR(30), @MiddleName NVARCHAR(20), @LastName NVARCHAR(30);
This query creates three variables named @FirstName, @MiddleName, and @LastName and gives each of the variable a default value of NULL.
To give an SQL Server variable a value, you can use an assignment after DECLARE:
1
DECLARE @MyCounter INT = 0;
The above line initializes the @MyCounter variable to 0.
Otherwise, you can SET on an already defined SQL variable as follows:
1
SET @MyCounter = 0;
Oracle
PL/SQL—Oracle's procedural extension to standard SQL—supports the use of variables. Once you define an SQL variable in Oracle within a procedural statement, you can use it wherever an expression is valid.
Oracle variables are defined using the DECLARE statement. Below is an example of how to use it:
1
DECLARE
2
v_hiredate DATE;
3
v_location VARCHAR2(15) := 'Paris';
4
v_deptno NUMBER(2) NOT NULL := 8;
5
c_comm CONSTANT NUMBER := 42;
Variables in DbVisualizer
Declaring variables in SQL is certainly beneficial, but it presents some challenges. The primary issue is that not all databases let you use them, and each database has its own syntax for implementation.
Oftentimes, you want to use variables just like parameterized SQL statements. Well, this is where DbVisualizer variables come into play!
DbVisualizer is a robust database client that supports traditional SQL variables and also provides numerous other features. Among these vast set of capabilities, it allows the definition of custom variables for parameterized queries, as illustrated in this example:
1
SELECT *
2
FROM employees
3
WHERE name LIKE '${First Name || Phil}$' AND age > ${Age || 20}$
The full syntax for declaring DbVisualizer variables is:
1
${name || value || type || options}$
These variables enable you to create parameterized SQL statements. When executing such a query in DbVisualizer, you will then be prompted to fill in some values for these variables:
This feature is particularly useful for running the same SQL query multiple times with different data inputs.
Conclusion
In this guide, you saw what an SQL variable is, when it is useful, and how to declare it in MySQL, PostgreSQL, SQL Server, and Oracle. You now know that SQL variables are a powerful tool to write complex logic in stored procedures, functions, and more.
As shown here, DbVisualizer takes variable management to the next level by supporting parameterized SQL queries. This feature allows you to write a query once and execute it with different variable values to get varying results each time.
DbVisualizer is a fully-featured database client that supports multiple DBMS technologies, offers advanced query optimization capabilities, and can generate ERD-type schemas with a few clicks. Try it for free!
FAQ
Are SQL variables part of the ISO/ANSI SQL standard?
SQL variables are not a formal part of the ISO/ANSI SQL standard. Thus, while several databases give you the ability to define an SQL variable, the standard itself does not mandate their presence or behavior. That is also why popular databases have differences in how they handle variables.
How do you set a primary key on a table variable in SQL Server?
In SQL Server, you can set the primary key on a table variable by specifying it within the declaration of the variable. In particular, you can use the PRIMARY KEY constraint to designate one or more columns as the primary key, as shown in this example:
1
DECLARE @MyTableVariable TABLE (
2
Id INT PRIMARY KEY,
3
Name NVARCHAR(50)
4
);
The above logic ensures that the Id column uniquely identifies each row. The same approach can be used to add foreign keys. Learn more in our comparison of primary keys vs. foreign keys.
What is the scope of an SQL variable?
The scope of an SQL variable depends on the database management system and the context in which it is defined. Typically, the scope of a variable goes from the point it is declared to the end of the batch or stored procedure it lives in.
How can declaring a variable in SQL improve a statement?
Declaring a variable in SQL can improve a statement by enhancing readability and maintainability. Variables can store intermediate results, making complex queries easier to understand and deal with. Also, they also reduce redundancy by supporting the reuse of values. Additionally, SQL variables simplify maintenance and updates, as changes only need to be made in one place rather than in the entire query.
Do all relational databases support SQL variables?
Since SQL variables are not part of the SQL standard, not all relational databases support them. While most major systems like MySQL, PostgreSQL, and SQL Server do support SQL variables, the implementation details and syntax can vary from database to database. Consult your specific type of DBMS for more details.
