SQL Server variables are temporary storage locations used to hold data during the execution of a batch, stored procedure, or function. They are essential for writing dynamic scripts and managing complex logic.

1. Local Variables

Local variables are defined by the user and are prefixed with a single @ symbol. Their scope is limited to the specific batch or procedure where they are declared.

Lifecycle: Declare, Assign, and Use

  • DECLARE: You must first define the variable name and its data type.
  • Assignment: Give the variable a value using SET, SELECT, or an inline assignment.
  • Usage: Use the variable name in your SQL logic.

Example:

SQL

-- Declare multiple variables in one statement
DECLARE @MinPrice MONEY = 10.00, 
        @MaxPrice MONEY, 
        @CategoryName NVARCHAR(50) = 'Electronics';

-- Assign a value using SET
SET @MaxPrice = 500.00;

-- Use variables in a query
SELECT ProductID, ProductName, Price
FROM Products
WHERE Price BETWEEN @MinPrice AND @MaxPrice
  AND Category = @CategoryName;

2. Setting Values: SET vs. SELECT

There are two ways to assign data to a variable. Choosing the right one is critical for error prevention.

Feature SET SELECT
Standard ANSI Standard (Best for portability). Non-standard (T-SQL specific).
Multiple One variable per SET statement. Can assign multiple variables at once.
Multi-row Query Fails with an error if query returns >1 row. Assigns the last row’s value (no error).
No-row Query Assigns NULL to the variable. Variable retains its previous value.

Example of SELECT for multiple assignments:

DECLARE @TotalCount INT, @AvgPrice MONEY;

SELECT @TotalCount = COUNT(*), 
       @AvgPrice = AVG(Price)
FROM Products;

3. Table Variables

A table variable stores a whole result set in memory. It behaves like a temporary table but is automatically cleaned up when the batch finishes.

Example:

-- Declare the table structure
DECLARE @DiscountedProducts TABLE (
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    NewPrice MONEY
);

-- Insert data into it
INSERT INTO @DiscountedProducts (ID, Name, NewPrice)
SELECT ID, Name, Price * 0.9 
FROM Products 
WHERE StockCount > 100;

-- Use it like a regular table
SELECT * FROM @DiscountedProducts WHERE NewPrice < 50;

4. Global Variables (System Functions)

These are built-in system functions that return information about the server or current session. They always start with @@. You cannot declare or modify these.

  • @@ERROR: Returns the error code of the last statement (0 if successful).
  • @@IDENTITY: Returns the last identity value (auto-increment) generated in the session.
  • @@ROWCOUNT: Returns how many rows were affected by the last query.
  • @@SERVERNAME: Returns the name of the local SQL Server instance.

Usage Example:

INSERT INTO Customers (Name) VALUES ('Jane Doe');

-- Capture the new ID immediately
DECLARE @NewID INT = @@IDENTITY; 

PRINT 'New Customer ID is ' + CAST(@NewID AS VARCHAR);

Categorized in:

Stored Procedures,