Select Into A Variable Sql Server

How to Select Into a Variable in SQL Server

If you work with SQL Server, you may need to capture the result of a query into a variable for later use in your batch or procedure. Also, the SELECT statement can be combined with DECLARE and SET to store a single value, a scalar result, or even a result set into a variable. Understanding the proper syntax and the underlying mechanics helps you write cleaner, more efficient T-SQL code and avoid common pitfalls such as data type mismatches or scope issues.

Introduction

In SQL Server, the SELECT statement is primarily used to retrieve data from tables. Still, when you need to reuse that data within the same session, storing it in a variable becomes essential. This article walks you through the different ways to select data into a variable, explains the science behind each method, and provides practical examples you can copy directly into your own queries.

Steps to Select Into a Variable

1. Declare the Variable

The first step is to declare a variable using the DECLARE keyword. You must specify a data type that can hold the expected result Took long enough..

DECLARE @MyVariable int;

• @MyVariable – the variable name (prefixed with @).
• int – the data type (choose the appropriate type for your data).

2. Assign a Value with SET

For a simple scalar value, you can use SET to assign the result of a SELECT into the variable Easy to understand, harder to ignore..

DECLARE @MyVariable int;
SELECT @MyVariable = MAX(Salary) 
FROM Employees;

• SELECT @MyVariable = … – this syntax stores the first row’s value into the variable.
• If the SELECT returns more than one row, only the first row’s value is kept.

3. Using SELECT … INTO for Result Sets

When you need to capture an entire result set, use SELECT … INTO (note the space between SELECT and INTO). This creates a new temporary table that holds the rows.

SELECT * INTO #TempResult
FROM Employees
WHERE DepartmentID = 5;

• #TempResult – a temporary table (local to the session).
• This method is useful when you plan to manipulate or join the result later.

4. Assigning Multiple Variables Simultaneously

You can assign values to several variables in a single SELECT statement by separating each assignment with a comma Easy to understand, harder to ignore. That alone is useful..

DECLARE @EmployeeID int, @FullName nvarchar(100);
SELECT @EmployeeID = EmployeeID, @FullName = FirstName + ' ' + LastName
FROM Employees
WHERE EmployeeID = 1;

• This reduces the number of statements and can improve readability.

5. Using OUTPUT Clause with INSERT/UPDATE

If you need to capture an identity value or a computed column after an INSERT or UPDATE, the OUTPUT clause can store the result directly into a variable But it adds up..

DECLARE @NewID int;
INSERT INTO Employees (FirstName, LastName)
OUTPUT INSERTED.EmployeeID INTO @NewID
VALUES ('John', 'Doe');

• OUTPUT INSERTED.EmployeeID INTO @NewID – captures the newly generated ID.

6. Handling NULL Results

When a query returns NULL, the variable will be set to NULL unless you provide a default value using ISNULL or COALESCE.

DECLARE @Salary decimal(10,2);
SELECT @Salary = ISNULL(MAX(Salary), 0)
FROM Employees;

• This prevents unexpected NULL values in subsequent calculations.

Scientific Explanation

How SELECT Into Works Internally

The SELECT … INTO statement creates a new table by copying the schema and data from the source query. Internally, SQL Server allocates a new storage object, populates it with rows, and then makes it available for subsequent operations. Because it creates a physical table (or a temporary table when prefixed with #), it can be indexed, joined, and updated—just like any regular table And it works..

Variable Scope and Lifetime

Variables declared with DECLARE are session‑scoped. And they exist for the duration of the batch, procedure, or transaction in which they are declared. If you declare a variable inside a stored procedure, it lives only for that procedure call unless you use OPTION RECOMPILE or other advanced scoping tricks.

Data Type Matching

SQL Server is strict about data type compatibility. When you assign a value to a variable, the result must be implicitly convertible to the variable’s declared type. Take this: assigning a VARCHAR longer than the declared NVARCHAR(50) will cause a truncation error And it works..

DECLARE @Code varchar(10);
SELECT @Code = CAST(EmployeeID AS varchar(10)) FROM Employees;

Practical Examples

Example 1: Capturing a Single Scalar Value

DECLARE @AvgSalary decimal(10,2);
SELECT @AvgSalary = AVG(Salary) FROM Employees;
PRINT 'Average Salary: ' + CAST(@AvgSalary AS varchar(20));

• This prints the average salary to the messages tab.

Example 2: Storing a Result Set for Later Use

DECLARE @TempTable TABLE (
    EmployeeID int,
    FullName nvarchar(150)
);

INSERT INTO @TempTable
SELECT EmployeeID, FirstName + ' ' + LastName
FROM Employees
WHERE HireDate > '2022-01-01';

SELECT * FROM

`@TempTable;`

- This pattern is ideal for staging data inside a batch or procedure without creating permanent `#temp` tables.

### Example 3: Capturing Identity Values with OUTPUT

```sql
DECLARE @InsertedIDs TABLE (EmployeeID int);

INSERT INTO Employees (FirstName, LastName, DepartmentID)
OUTPUT INSERTED.EmployeeID INTO @InsertedIDs
VALUES 
    ('Alice', 'Smith', 10),
    ('Bob', 'Jones', 20),
    ('Carol', 'White', 10);

SELECT EmployeeID FROM @InsertedIDs;

• The table variable @InsertedIDs holds every identity value generated by the multi-row INSERT, allowing immediate downstream processing (e.g., inserting into a child table).

Example 4: Assigning Variables from a Rowset Safely

DECLARE @TopEarnerName nvarchar(150), @TopEarnerSalary decimal(10,2);

SELECT TOP (1) 
    @TopEarnerName = FirstName + ' ' + LastName,
    @TopEarnerSalary = Salary
FROM Employees
ORDER BY Salary DESC;

IF @TopEarnerName IS NOT NULL
    PRINT 'Top earner: ' + @TopEarnerName + ' (