how to declare variable in TSQL and use in multiple statements

Declaring and Using Variables in T-SQL Across Multiple Statements

Learn the fundamentals of declaring and effectively using variables in T-SQL to store data, enhance readability, and improve performance within your SQL Server scripts and stored procedures.

Variables are essential constructs in any programming language, and T-SQL is no exception. They provide a way to store data values temporarily within a batch, stored procedure, or function, allowing for more dynamic, readable, and maintainable code. This article will guide you through the process of declaring variables, assigning values, and using them effectively across multiple statements in T-SQL.

Declaring T-SQL Variables

Before you can use a variable in T-SQL, you must declare it. The DECLARE statement is used for this purpose, specifying the variable's name and its data type. Variable names in T-SQL must start with an at sign (@). It's good practice to choose descriptive names that reflect the variable's purpose.

DECLARE @EmployeeID INT;
DECLARE @FirstName NVARCHAR(50);
DECLARE @HireDate DATE;
DECLARE @TotalSales DECIMAL(10, 2);

Examples of declaring variables with different data types.

Assigning Values to Variables

Once declared, a variable can be assigned a value using either the SET statement or the SELECT statement. Both methods achieve the same result, but SELECT can assign values to multiple variables simultaneously and is often used when retrieving values from a query.

-- Using SET to assign a value
DECLARE @ProductName NVARCHAR(100);
SET @ProductName = 'Laptop Pro X';
SELECT @ProductName AS AssignedProduct;

-- Using SELECT to assign a value
DECLARE @MinPrice DECIMAL(10, 2);
SELECT @MinPrice = 500.00;
SELECT @MinPrice AS MinimumPrice;

-- Using SELECT to assign from a query
DECLARE @OrderCount INT;
SELECT @OrderCount = COUNT(*) FROM Sales.Orders WHERE OrderDate >= '2023-01-01';
SELECT @OrderCount AS OrdersIn2023;

Demonstrating value assignment using SET and SELECT.

Using Variables in Multiple Statements

The true power of variables lies in their ability to store a value once and then reuse it across multiple subsequent statements within the same execution scope. This avoids repeating literal values, makes code easier to modify, and can be crucial for building complex logic, filtering data, or controlling flow.

flowchart TD
    A[Declare @CustomerID INT] --> B{SET @CustomerID = 101};
    B --> C[SELECT * FROM Customers WHERE CustomerID = @CustomerID];
    C --> D[INSERT INTO OrderLog (CustomerID, LogDate) VALUES (@CustomerID, GETDATE())];
    D --> E[UPDATE CustomerStats SET LastActivity = GETDATE() WHERE CustomerID = @CustomerID];
    E --> F[End Process];

Flowchart illustrating variable usage across multiple T-SQL statements.

DECLARE @TargetDate DATE;
SET @TargetDate = '2023-06-15';

-- Statement 1: Select orders placed on the target date
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE OrderDate = @TargetDate;

-- Statement 2: Count products sold on the target date
SELECT COUNT(DISTINCT ProductID) AS ProductsSold
FROM Sales.OrderDetails od
JOIN Sales.Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate = @TargetDate;

-- Statement 3: Update a status based on the target date
UPDATE Sales.DailySummary
SET Processed = 1
WHERE SummaryDate = @TargetDate;

Example of a variable being used in three different SQL statements.