SQL variables are an essential concept in Microsoft SQL Server that every database administrator and developer should understand. In this tutorial, we will explore the fundamentals of SQL variables, including their declaration, assignment, and usage.

Declaration of SQL Variables

Unlike objects in SQL Server that are created and persist even after closing the SQL instance, SQL variables are declared and exist only during the execution of a script. To declare a variable, we use the “DECLARE” keyword followed by the variable name preceded by the “@” symbol. We also specify the data type and optionally assign an initial value to the variable.

The basic syntax for declaring a SQL variable is as follows:

DECLARE @variable_name data_type = value;

We can also declare a variable first and assign its value later using the “SET” statement:

DECLARE @variable_name data_type;
SET @variable_name = value;

It’s important to note that while the value of a variable can be changed at any time, the data type and name of a variable cannot be altered.

Assigning Values to SQL Variables

There are two options available to assign a value to a SQL variable: “SET” and “SELECT” statements.

The “SET” statement is used to assign a specific value to a variable. For example:

DECLARE @testvalue1 VARCHAR(20);
SET @testvalue1 = 'Hello World!';

The “SELECT” statement can also be used to assign values to variables. For instance:

DECLARE @testvalue2 AS VARCHAR(20);
SELECT @testvalue2 = [FirstName]
FROM Person.Person
WHERE BusinessEntityID = 8;

Multiple variables can be declared and assigned values simultaneously by separating them with commas:

DECLARE @testvalue2 AS VARCHAR(20), @testvalue3 AS VARCHAR(20);
SELECT 
   @testvalue2 = [FirstName],
   @testvalue3 = [LastName]
FROM Person.Person
WHERE BusinessEntityID = 8;

Additionally, SQL variables can be assigned values from scalar-valued functions:

DECLARE @testvalue3 AS INT;
SELECT @testvalue3 = dbo.ufnGetStock(3);

Scope of SQL Variables

It’s important to understand that SQL variables are only available within the scope of a query execution. Once a batch is terminated, such as with the “GO” batch separator, the variables can no longer be referenced.

For example, if we terminate a batch and then try to print the value of a variable, an error will occur:

DECLARE @testvalue1 VARCHAR(20);
SELECT @testvalue1 = 'Hello World!';
PRINT @testvalue1;

PRINT @testvalue1;

In the above example, the print statement inside the batch runs successfully, but the print statement outside the batch fails because the variable no longer exists.

Conclusion

In this tutorial, we have covered the basics of SQL variables in Microsoft SQL Server. We have learned how to declare variables, assign values to them using “SET” and “SELECT” statements, and understand their scope within a query execution. SQL variables are powerful tools that allow for dynamic and flexible scripting in SQL Server.

Article Last Updated: 2022-12-21