How to use SQL Server Variables? T-SQL

Managing data efficiently is at the core of any well-structured database system. When working with Microsoft SQL Server, an integral part of this management is the effective use of SQL Server variables. These variables allow for the temporary storage of data that can be manipulated or used throughout your session. In this tutorial, we will explore how to declare, assign, and manipulate SQL Server variables using a practical example. Before diving into variables, we’ll start by creating and populating a sample table that we’ll use throughout this tutorial.

Table of Contents

1. Create and Populate the Sample Table

Begin by creating a new table named ‘Employees’. This table will have three columns: ‘EmployeeID’, ‘FirstName’, and ‘LastName’. To create this table, execute the following SQL statement:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

After the creation of the ‘Employees’ table, you can populate it with some sample data. This data will help illustrate how variables can be used in SQL Server.

INSERT INTO Employees (EmployeeID, FirstName, LastName) 
VALUES 
(1, 'John', 'Doe'),
(2, 'Jane', 'Doe'),
(3, 'James', 'Smith');

You now have a table filled with data, ready for manipulation with SQL Server variables.

2. Declare SQL Server Variables

To declare a variable in SQL Server, use the DECLARE statement. Variables start with an ‘@’ symbol. For instance, to declare a variable that can hold an integer value, you could write:

DECLARE @EmployeeCount INT;

This statement declares a variable named ‘@EmployeeCount’ that can hold an integer.

3. Assign a Direct Value to a SQL variable

In the previous section, we assigned a simple value to a SQL variable using the SET statement and a SELECT query. MS SQL, however, offers a variety of ways to assign values to variables. After declaring a variable, assign a value to it using the SET statement.

You can assign a direct value to a variable. This is useful when the value you want to store doesn’t need to be derived from a SQL query.

DECLARE @Greeting NVARCHAR(50);
SET @Greeting = 'Hello, SQL Server!';
PRINT @Greeting;

In this example, the string ‘Hello, SQL Server!’ is directly assigned to the @Greeting object.

4. Assign one value from a SELECT statement

As demonstrated earlier, you can assign a value to a variable based on a SQL query’s result. The value from the query is set as the variable’s value.

DECLARE @OldestEmployeeID INT;
SET @OldestEmployeeID = (SELECT TOP 1 EmployeeID FROM Employees ORDER BY DateOfBirth ASC);
PRINT @OldestEmployeeID;

Here, the variable @OldestEmployeeID is set to the ID of the oldest employee in the ‘Employees’ table.

5. Assign multiple values from a SELECT

SQL Server allows you to assign values to multiple variables using a single SELECT statement.

DECLARE @MinSalary DECIMAL(18, 2), @MaxSalary DECIMAL(18, 2);
SELECT @MinSalary = MIN(Salary), @MaxSalary = MAX(Salary) FROM Salaries;
PRINT 'Minimum salary: ' + CAST(@MinSalary AS NVARCHAR(20));
PRINT 'Maximum salary: ' + CAST(@MaxSalary AS NVARCHAR(20));

This assigns the minimum and maximum salary from the ‘Salaries’ table to the @MinSalary and @MaxSalary variables, respectively.

6. Assign values using SELECT INTO

While SELECT INTO is often used to create a new table from the result set of a SELECT statement, you can also use it to assign a value to a variable.

DECLARE @AverageSalary DECIMAL(18, 2);
SELECT @AverageSalary = AVG(Salary) FROM Salaries INTO @AverageSalary;
PRINT 'Average salary: ' + CAST(@AverageSalary AS NVARCHAR(20));

In this case, the average salary from the ‘Salaries’ table is assigned to the @AverageSalary variable.

By exploring these various ways to assign values, you can gain flexibility in how you manipulate and use data with SQL Server variables.

7. Using Variables in T-SQL Queries

SQL Server variables can be used in any SQL statement or stored procedure. For example, to display the total number of employees, use the PRINT statement:

PRINT 'The total number of employees is: ' + CAST(@EmployeeCount AS NVARCHAR(10));

This statement converts the @EmployeeCount variable to a string and concatenates it with a message, resulting in a string that provides the total number of employees.

Conclusion

In this tutorial, you learned how to create and populate a sample table in SQL Server. More importantly, you learned how to declare, assign, and use SQL Server variables. This basic understanding of variables enables you to write more dynamic and efficient SQL scripts.

Remember, variables offer a powerful way to temporarily store and manipulate data within a session. Combined with SQL temporary tables and views, they offer a lot of flexibility to manipulate and represent the data. As you become more comfortable with them, you will find they are invaluable tools in your SQL Server toolkit.