Creating and using variables

Like other programming languages, the SQL Server Transact-SQL language also allows temporary storage in the form of variables. Variables are stored in memory and are accessible only from the batch or stored procedure, or the function in which they are declared. There are three types of variables you can create in SQL Server: local variables (based on system or user-defined data types), cursor variables (to store a server-side cursor), and table variables (that is, structured like a user-defined table).

We can declare a variable as a standard variable in Transact-SQL by prefixing it with the @ symbol. We use the DECLARE statement to declare a variable or multiple variables.

Creating a local variable

The basic syntax for creating a local variable is as follows:

DECLARE @variable_name [AS] data_type

By default, all local variables are initialized as NULL. We can assign a value to a local variable in one of the following three ways:

• By using the SET keyword, which is the...