How to Declare and Assign Value to a Variable in SQL Server

Tutorialsrack 05/02/2025 SQL SQL Server

Introduction

When working with SQL Server, variables play a crucial role in storing and manipulating temporary data during query execution. Understanding how to declare and assign values to variables is essential for writing efficient and maintainable SQL scripts.

In this guide, we will explore different ways to declare and assign values to variables in SQL Server, with practical examples and real-world use cases.

Why Use Variables in SQL Server?

Using variables in SQL Server provides several benefits:

• Enhances Readability: Makes SQL scripts easier to read and maintain.
• Improves Performance: Reduces redundant calculations and optimizes queries.
• Facilitates Data Manipulation: Stores temporary values that can be used throughout the execution of a script.
• Supports Dynamic Querying: Helps in building dynamic SQL queries based on user input or calculations.

Declaring Variables in SQL Server

To declare a variable in SQL Server, use the DECLARE statement followed by the variable name and data type.

Syntax:

DECLARE @variable_name data_type;

Example:

DECLARE @EmployeeID INT;
DECLARE @EmployeeName VARCHAR(100);
DECLARE @Salary DECIMAL(10,2);

Here, we declare three variables:

• @EmployeeID of type INT
• @EmployeeName of type VARCHAR(100)
• @Salary of type DECIMAL(10,2)

Assigning Values to Variables

Once declared, values can be assigned to variables using different methods.

1. Using SET Statement

The SET statement assigns a single value to a variable.

Syntax:

SET @variable_name = value;

Example:

DECLARE @Department VARCHAR(50);
SET @Department = 'HR';
PRINT @Department;

Pros:

• Ensures only one value is assigned.
• Recommended for assigning a single value.

2. Using SELECT Statement

The SELECT statement can also be used to assign values to variables.

Syntax:

SELECT @variable_name = value;

Example:

DECLARE @TotalEmployees INT;
SELECT @TotalEmployees = COUNT(*) FROM Employees;
PRINT @TotalEmployees;

Pros:

• Allows assignment from queries.
• Can assign multiple variables in a single query.

Cons:

• May lead to unexpected results if the query returns multiple rows.

3. Assigning Values from a Query

Sometimes, we need to assign values based on a query result.

Example:

DECLARE @EmployeeName VARCHAR(100);
SELECT @EmployeeName = EmployeeName FROM Employees WHERE EmployeeID = 101;
PRINT @EmployeeName;

Real-World Examples and Use Cases

Example 1: Storing Temporary Results

DECLARE @TotalSales DECIMAL(10,2);
SELECT @TotalSales = SUM(SalesAmount) FROM Orders WHERE OrderDate = '2024-02-05';
PRINT 'Total Sales: ' + CAST(@TotalSales AS VARCHAR(20));

Example 2: Using Variables in Dynamic Queries

DECLARE @TableName VARCHAR(50);
SET @TableName = 'Employees';
EXEC('SELECT * FROM ' + @TableName);

Example 3: Using Variables in Conditional Logic

DECLARE @Stock INT;
SELECT @Stock = Quantity FROM Inventory WHERE ProductID = 5001;
IF @Stock < 10
    PRINT 'Low Stock! Reorder Required';
ELSE
    PRINT 'Stock Level is Sufficient';

Key Takeaways

• DECLARE Statement: Used to define a variable with a specific data type.
• SET vs SELECT:
• Use SET for assigning single values.
• Use SELECT for fetching values from queries.
• Multiple Rows in SELECT: If the query returns multiple rows, only the last value is assigned.
• Variables Enhance Performance: Reduce redundant calculations and improve query efficiency.
• Use Cases: Useful for calculations, dynamic SQL, and conditional logic.

Summary

In SQL Server, declaring and assigning values to variables is a fundamental concept for writing efficient queries. The DECLARE statement is used to create variables, while SET and SELECT are used to assign values. Understanding these techniques can help in optimizing query performance and making SQL scripts more maintainable.

By implementing these best practices, you can leverage variables effectively in your SQL queries for better performance and readability.

Related Posts