Understanding Local Variables and Global Variables in SQL Server

Tutorialsrack 07/02/2025 SQL SQL Server

Introduction

In SQL Server, variables are fundamental tools for temporarily storing data and controlling the flow of your queries. Whether you're writing simple scripts or developing complex stored procedures, understanding how to work with variables is essential. In this guide, we will delve into local variables and global variables—two distinct types that play unique roles in SQL Server. This article helps you to master both local and global variables.

What Are Local and Global Variables?

In SQL Server, variables allow you to hold data temporarily during query execution. However, not all variables are created equal. They fall into two main categories:

Local Variables

• Definition: Local variables are user-defined variables that you declare within a specific batch, stored procedure, or function.
• Scope: They are accessible only within the batch or procedure where they are declared.
• Lifetime: They exist only for the duration of that batch or procedure.
• Syntax: Local variables are defined using the DECLARE statement and are prefixed with an @ symbol (e.g., @MyVariable).

Global Variables

• Definition: Global variables in SQL Server are system-defined variables that provide information about the system state. They are sometimes referred to as system functions or system variables.
• Scope: They are accessible from any session or batch within the SQL Server instance.
• Lifetime: They are available throughout the duration of the session and cannot be modified by users.
• Syntax: Global variables are read-only and are prefixed with @@ (e.g., @@ROWCOUNT, @@ERROR).

Detailed Examples with Explanations

Local Variables

Local variables are the workhorses of SQL scripting. They allow you to store temporary data and use it within your queries.

Example 1: Declaring and Assigning a Local Variable

-- Declare a local variable
DECLARE @EmployeeCount INT;

-- Assign a value to the variable using SET
SET @EmployeeCount = (SELECT COUNT(*) FROM Employees);

-- Use the variable in a SELECT statement
SELECT @EmployeeCount AS 'Total Employees';

Explanation:

• Declaration: The variable @EmployeeCount is declared as an integer.
• Assignment: The SET statement assigns the count of employees from the Employees table to the variable.
• Usage: The value of @EmployeeCount is then selected and displayed.

Example 2: Assigning Multiple Values with SELECT

-- Declare multiple local variables
DECLARE @TotalSales DECIMAL(10,2), @AverageSales DECIMAL(10,2);

-- Assign values using SELECT from a query
SELECT 
    @TotalSales = SUM(SalesAmount), 
    @AverageSales = AVG(SalesAmount)
FROM Sales;

-- Display the results
SELECT @TotalSales AS 'Total Sales', @AverageSales AS 'Average Sales';

Explanation:

• Multiple Variables: Two variables are declared in a single statement.
• Assignment via SELECT: Both variables are assigned values from a query that aggregates sales data.
• Output: The final SELECT displays the total and average sales.

Global Variables

Global variables are built into SQL Server and provide useful information about the server’s state.

Example 1: Using Global Variable @@ROWCOUNT

-- Run a sample query
SELECT * FROM Orders;

-- Retrieve the number of rows affected by the previous query
SELECT @@ROWCOUNT AS 'Rows Affected';

Explanation:

• @@ROWCOUNT: This global variable holds the number of rows affected by the last executed statement.
• Usage: It is useful for conditional logic and error handling, allowing you to determine if an operation was successful.

Example 2: Using Global Variable @@ERROR

-- Attempt to insert a duplicate key into a table with a unique constraint
INSERT INTO Products (ProductID, ProductName) VALUES (101, 'Gadget');

-- Check if an error occurred
SELECT @@ERROR AS 'Error Code';

Explanation:

• @@ERROR: This global variable returns the error number of the last executed statement.
• Usage: It is a crucial component for error handling in T-SQL scripts, letting you capture and respond to runtime errors.

Real-World Examples and Use Cases

Example 1: Using Local Variables in Stored Procedures

Imagine you need to calculate and log the monthly sales within a stored procedure. Local variables can hold intermediate values such as total sales and average sales, making your procedure modular and easier to debug.

CREATE PROCEDURE CalculateMonthlySales
    @Month INT,
    @Year INT
AS
BEGIN
    DECLARE @TotalSales DECIMAL(10,2);
    DECLARE @AverageSales DECIMAL(10,2);

    SELECT 
        @TotalSales = SUM(SalesAmount),
        @AverageSales = AVG(SalesAmount)
    FROM Sales
    WHERE MONTH(SaleDate) = @Month AND YEAR(SaleDate) = @Year;

    PRINT 'Total Sales: ' + CAST(@TotalSales AS VARCHAR(20));
    PRINT 'Average Sales: ' + CAST(@AverageSales AS VARCHAR(20));
END;

Explanation:
This stored procedure demonstrates the practical use of local variables to store and manipulate data based on input parameters.

Example 2: Using Global Variables for Error Handling

Global variables like @@ERROR and @@ROWCOUNT are invaluable for tracking the state of your SQL operations. For instance, after executing a critical update, you might want to verify how many rows were modified and whether any errors occurred.

UPDATE Inventory
SET Quantity = Quantity - 1
WHERE ProductID = 2001;

IF @@ERROR <> 0
    PRINT 'An error occurred during the update operation.';

PRINT 'Rows affected: ' + CAST(@@ROWCOUNT AS VARCHAR(10));

Explanation:
This snippet shows how global variables help in checking the success of an update statement, allowing for immediate error handling.

Key Takeaways

• Local Variables:
• Declared using the DECLARE statement with a preceding @ symbol.
• Their scope is limited to the batch, procedure, or function in which they are defined.
• They can be assigned values using the SET or SELECT statements.
• Global Variables:
• System-defined and read-only, prefixed with @@.
• Provide critical system information such as @@ROWCOUNT and @@ERROR.
• Available across all sessions, but SQL Server does not support user-defined global variables.
• Usage:
• Use local variables for temporary storage and manipulation of data within your scripts.
• Leverage global variables for system-level information and error handling.

Summary

In SQL Server, understanding the difference between local and global variables is essential for writing efficient, maintainable, and robust T-SQL scripts. Local variables, declared with an @ symbol, are user-defined and only accessible within the scope in which they are declared. They are perfect for storing temporary data during script execution. In contrast, global variables (prefixed with @@) are system-defined, read-only, and provide valuable insights into the SQL Server environment, such as the number of rows affected by a query or error codes from the last operation.

By mastering both local and global variables, you can build dynamic SQL scripts that are both powerful and adaptable to a variety of scenarios. Whether you're developing stored procedures, performing error handling, or simply running queries, these tools are indispensable in your SQL Server toolkit.

Related Posts