In SQL Server, variables play a critical role in the dynamic execution of SQL scripts and procedures. Variables allow you to store and manipulate data temporarily within the scope of a batch or procedure. By using the DECLARE statement, you can create variables with specific data types, which can then be assigned values using the SET or SELECT commands.
In this article, We will learn about How to declare a variable in SQL Server by understanding various scenarios in detail.
How to Declare a Variable in SQL Server
To declare a variable in SQL Server, use the DECLARE statement. Variables must be prefixed with an @ symbol and must be assigned a data type.
Syntax:
DECLARE @VariableName DataType;
Example:
DECLARE @EmployeeID INT;
DECLARE @EmployeeName VARCHAR(100);
Assigning a Value to a Variable
After declaring a variable, we can assign a value to it using the SET or SELECT statement.
Using SET Command to Declare Variable in SQL Server
Syntax:
SET @VariableName = Value;
Example:
SET @EmployeeID = 1;
SET @EmployeeName = 'John Doe';
Using SELECT:
Syntax:
SELECT @VariableName = ColumnName FROM TableName WHERE Condition;
Example:
SELECT @EmployeeName = Name FROM Employees WHERE ID = @EmployeeID;
Using Variables in SQL Statements
Variables can be used in various SQL statements like SELECT, INSERT, UPDATE, and DELETE.
Example:
Update Statement:
UPDATE Employees
SET Name = @EmployeeName
WHERE ID = @EmployeeID;
Output:
Number of rows affected by the update operation.
Select Statement:
SELECT ID, Name FROM Employees WHERE ID = @EmployeeID;
Output:
Displays the ID and Name of the employee with the given @EmployeeID.
If @EmployeeID = 1 and Name = 'John Doe', the output would be:
Variable Scope and Lifetime
Scope: Local to batch, stored procedure, or function where declared. The variables are not accessible outside their declaring scope.
Lifetime: Till the end of the batch or procedure. The variable is not available any more after the execution is over.
Example:
BEGIN
DECLARE @TempVar INT;
SET @TempVar = 100;
-- @TempVar is accessible here
END;
-- @TempVar is not accessible here
Output:
No output outside the BEGIN...END block.
Using With Clause to Declare Variable in SQL Server
The WITH clause in SQL Server is primarily used for defining Common Table Expressions, which are temporary result sets that can be referenced in the SELECT, INSERT, UPDATE, or DELETE statement. That means the WITH clause will not be used to declare variables. Variables will be declared in SQL Server using the DECLARE statement.
Declaring and Using ariables in SQL Server
The example below shows how variables can be declared and used within SQL Server:
Variable Declaration:
DECLARE @MyVariable INT;
Assign a Value to the Variable:
SET @MyVariable = 10;
Use the Variable in a Query:
SELECT @MyVariable AS MyValue;
Example: Use Variable in a SELECT Statement
DECLARE @CustomerID INT;
SET @CustomerID = 1;
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID;
CTE with the WITH Clause
The WITH clause is used for defining a temporary result set, known in this context as a common table expression (CTE), which you can then refer to within the execution of a single SQL statement.
Not for declaring variables, but quite handy in breaking down a complex query into manageable pieces.
Example of a CTE with the WITH Clause:
WITH SalesCTE AS (
SELECT SalesPersonID, SUM(TotalDue) AS TotalSales
FROM SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales
FROM SalesCTE
WHERE TotalSales > 100000;
Mixing Variables with CTEs
You can use both variables and CTEs in the same query, but they accomplish quite different things. Variables contain single scalar values whereas CTEs structure complex queries.
Example Mixing Variables and CTEs:
DECLARE @MinSales DECIMAL(10, 2);
SET @MinSales = 100000.00;
WITH SalesCTE AS (
SELECT SalesPersonID, SUM(TotalDue) AS TotalSales
FROM SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales
FROM SalesCTE
WHERE TotalSales > @MinSales:
Using Temporary Variables to Declare Variable in SQL Server
in SQL Server, when you refer to "temporary variables," you probably mean variables in the scope of one batch, stored procedure, or function. They are only those declared using the DECLARE statement and are temporary in terms of living only during the life of the batch or session.
Declaring and Using Temporary Variables
Declaring a Variable:
DECLARE @MyVariable INT;
- @MyVariable – Variable Name
- INT -- Data Type
Value Assignment to the Variable:
SET @MyVariable = 100;
This statement will assign a value 100 to the @MyVariable.
Using Variable:
SELECT @MyVariable AS MyValue;
It will return the value of the @MyVariable.
Example of Using a Temporary Variable in a Query
Suppose, you want the records based on a value to be stored in a variable:
DECLARE @CustomerID INT;
SET @CustomerID = 5;
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID;
This example declares a variable @CustomerID and initializes it to a value of 5. The second step makes use of this variable together with the SELECT statement for filtering the records from the Orders table where CustomerID is equal to 5. Temporary Variables with Arithmetic or String Operations You can use variables for arithmetic or string operations: sql Copy code DECLARE @Price DECIMAL(10, 2); DECLARE @Quantity INT; DECLARE @Total DECIMAL(10, 2);
Using Subqueries to Declare Variable in SQL Server
We cannot declare a variable and directly assign it the result from a subquery in the DECLARE statement in SQL Server. However, we can set a variable with a subquery after it has been declared. This is often done with a SET or SELECT statement.
Example: Subquery with SET
DECLARE @TotalSales DECIMAL(10, 2);
SET @TotalSales = (SELECT SUM(TotalAmount) FROM Orders WHERE CustomerID = 1);
SELECT @TotalSales AS TotalSalesForCustomer;
In this example:
- A variable @TotalSales is declared.
- The SET statement assigns the result of a subquery – that calculates the total sales for a particular customer – to the variable.
- The content stored in @TotalSales is then selected and returned.
Example: Using a Subquery with SELECT
DECLARE @HighestPrice DECIMAL(10, 2);
SELECT @HighestPrice = (SELECT MAX(UnitPrice) FROM Products);=
SELECT @HighestPrice AS HighestProductPrice;
In this example:
- A variable @HighestPrice is declared.
- The SELECT statement simply assigns the result of a subquery, which finds the highest price for a product, to the variable. It then selects and returns this value stored in @HighestPrice.
Common Data Types
SQL Server supports various data types for variables:
- INT: Integer values
- VARCHAR(n): Variable-length character strings
- CHAR(n): Fixed-length character strings
- DATE: Dates
- FLOAT: Floating-point numbers
Conclusion
Variable declaration in SQL Server is an essential requirement to handle and process data efficiently in SQL scripts, stored procedures, and functions. Variables can be declared by using the DECLARE statement and then values set to them by SET or SELECT statements. The functioning or operation of temporary data, logic flow control, and execution of complicated queries can be performed in a better way. In order to manipulate variables, you must understand their scope of operation and lifetime. Variable declaration, in general, greatly enhances the capability of writing dynamic SQL code for efficiency, hence robust and manageable database applications.