T-SQL 101: 97 Defining and initializing variables in T-SQL

Variables in T-SQL work very much the way they do in other languages. A variable really is nothing more than a name given to a memory location. In T-SQL, variable names must start with an @ sign. An example would be:

@CustomerName

As well as defining a name, variables have other properties.

The first is that we have a data type. It defines the types of things, like strings or numbers and so on, that can we store in that location.

The second additional property is the value i.e., what is currently stored there.

And the third additional property is what’s called the scope. Now, the concept of a scope might be familiar to you, if you’ve worked with other languages. It’s where the variable is visible. In SQL Server, it means within the batch. That’s the set of statements that is optimized and executed together. So if you declare a variable in one batch, it disappears at the end of the batch, and it isn’t present in the next batch.

Now, the way we define variables is to use the DECLARE statement:

DECLARE @Age int;

In this case, I’ve defined a variable called @Age and said that it will hold integer values.

Now that value, when I haven’t set anything else, would be NULL at this stage. But we can do that differently:

DECLARE @Greeting varchar(20) = 'Hello';

This is an example of being able to initialize the value at the time you declare it. That was first introduced in SQL Server 2008. In the early days of SQL Server, it wasn’t available, and so you’ll often see values declared and then set separately in existing code:

DECLARE @Greeting varchar(20);
SET @Greeting = 'Hello';

Note that if you want want to change the value in a variable, you can use the SET statement.

SET can work with variables, both as a source of data and a target for it. For example:

SET @Age = @Age * 5;

I’ve said that I want to multiply the current value of @Age by 5.

There’s a shortcut version of that where I can say:

SET @Age *= 5;

That does exactly the same thing. There are also shortcuts like this for addition (+), subtraction (-), and division (/).

In other versions of the SQL language, there was no SET statement. Instead, you’ll often see SELECT used instead:

SELECT @Greeting = 'Hello';

If you’ve ever worked with Oracle, you’ll also notice there is no FROM clause in this SELECT statement. In Oracle-compatible languages, you’ll often see FROM dual at the end of statements like this. That is not required (or permitted) in SQL Server T-SQL.

Regardless, nowadays, I’d prefer to use SET for this.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-01-24