The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

DECLARE, SET and assignment with SELECT: working with variables

A reusable query should not force us to modify values scattered through the code. A variable retains a value under a name during its scope. It can represent a date, identifier, limit, or intermediate result inside a batch—the group of statements sent together to SQL Server—or a procedure.

DECLARE creates the variable. SET and SELECT can be assigned values, but they do not behave the same in all cases.

Constructs for variables

DECLARE: create a variable

DECLARE creates the name and establishes the data type the variable can accept. It can also assign an initial value in the same statement.

DECLARE @Variable TipoDeDato;
DECLARE @OtraVariable TipoDeDato = ValorInicial;

SET: assign one value

SET assigns an expression to an existing variable. The expression can be a value, a calculation, or a subquery that returns one value.

SET @Variable = Expresion;

SELECT: assign from a query

SELECT can assign one or more variables from values produced by a query. We must control how many rows can be returned because the assignment can repeat when several rows arrive.

SELECT
    @Variable1 = Expresion1,
    @Variable2 = Expresion2
FROM dbo.Tabla
WHERE Condicion;

Declare and assign

DECLARE @IdCategoria SMALLINT;
DECLARE @PrecioMaximo DECIMAL(12, 2);

SET @IdCategoria = 1;
SET @PrecioMaximo = 500.00;

The name starts with @ and the type defines what values ​​it can receive. We can also declare and initialize in a single line:

DECLARE @FechaDesde DATE = '2026-03-01';

Example: using variables in a query

DECLARE @IdCategoria SMALLINT = 1;
DECLARE @PrecioMaximo DECIMAL(12, 2) = 500.00;

SELECT
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.Producto AS p
WHERE p.IdCategoria = @IdCategoria
  AND p.Precio <= @PrecioMaximo
ORDER BY p.Precio;

Variables live only within their scope, the part of execution where the name exists. They are not parameters received automatically from an application; a procedure must declare them as part of its signature, the list of parameters it accepts.

Assign the result of a query

SET can receive a scalar subquery, meaning a query that returns one value:

DECLARE @PrecioPromedio DECIMAL(12, 2);

SET @PrecioPromedio =
(
    SELECT AVG(p.Precio)
    FROM dbo.Producto AS p
    WHERE p.Activo = 1
);

SELECT @PrecioPromedio AS PrecioPromedio;

SELECT allows you to assign several variables in the same query:

DECLARE @CantidadProductos INT;
DECLARE @PrecioMayor DECIMAL(12, 2);

SELECT
    @CantidadProductos = COUNT(*),
    @PrecioMayor = MAX(p.Precio)
FROM dbo.Producto AS p
WHERE p.Activo = 1;

Important differences

If a scalar subquery used by SET returns multiple rows, SQL Server generates an error. An assignment using SELECT @Variable = Columna FROM ... can repeatedly assign and end with one of the processed values; Without a design that guarantees a single row, the result does not represent a reliable choice.

When an assignment with SELECT finds no rows, the variable can retain its previous value. That is why we should not use it as if it always assigned NULL automatically.

What should be checked

The variable type must match the data it represents. Insufficient length can truncate text—lose the characters that do not fit—and an unsuitable scale, the number of decimal digits, can round numbers.

We also distinguish a local variable from a parameter and a column. Using descriptive prefixes such as @FechaDesde or @IdCliente makes it easier to recognize intent without adding unnecessary comments.