Variables and SET statements in SQL

Introduction

Variables let you store temporary values in your database session. SET statements assign values to these variables so you can use them later in your queries.

When you want to store a value to use multiple times in a query.

When you need to calculate a value once and reuse it in several places.

When you want to make your query easier to read by naming important values.

When you want to change a value quickly without rewriting the whole query.

Syntax

SQL

DECLARE @variable_name datatype;
SET @variable_name = value;

You must declare a variable before using SET to assign a value.

Variable names usually start with @ and have a datatype like INT or VARCHAR.

Examples

Declare an integer variable named @age and set its value to 30.

SQL

DECLARE @age INT;
SET @age = 30;

Declare a text variable @name with space for 50 characters and set it to 'Alice'.

SQL

DECLARE @name VARCHAR(50);
SET @name = 'Alice';

Declare a decimal variable @total and set it to 99.99.

SQL

DECLARE @total DECIMAL(10,2);
SET @total = 99.99;

Sample Program

This example declares a variable @count, sets it to 5, and then selects it to show the value.

SQL

DECLARE @count INT;
SET @count = 5;
SELECT @count AS NumberOfItems;

Important Notes

Variables exist only during the current session or batch.

You can change a variable's value by using SET again.

Some SQL systems also allow SELECT to assign variables, but SET is clearer.

Summary

Variables store temporary values in SQL queries.

Use DECLARE to create a variable and SET to assign a value.

Variables help make queries easier to write and understand.