We need to back to basics in our Transact SQL (T-SQL) studies and look at programming with variables.

SQL Server allows you to create reusable programming objects. The SQL Server reusable programming objects include

• Views
• Stored Procedures
• Functions
• Triggers

Temporary Variables

SQL Server Transact-SQL language also allows temporary storage in the form of variables which are stored in memory and are accessible only from the batch or stored procedure, or the function in which they are declared.

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. Here is the basic syntax. The AS is in square brackets because it is optional.

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 preferred method

  --Example of assigning a value to the local variable using the
  --SET keyword.
  DECLARE @var1 [int],
  @var2 [varchar](10);
  SET @var1 = 10
  SET @var2 = N'MyValue1';
  

• By using the SELECT statement

  --Example of assigning a value to the local variable using the
  --SELECT statement.
  DECLARE @var3 [int],
  @var4 [varchar](10);
  SELECT @var3 = 20 ,
  @var4 = N'MyValue2';
  

• During the declaration of the variable

  --Example of assigning a value to the local variable at
  --declaration.
  DECLARE @var5 [int] = 30,
  @var6 [varchar](10) = N'MyValue3';
  GO
  

Truncation

If you declare an INT and then give it a value of 12.4, what happens? SQL will not throw an error. It simply truncates the fractional part and in this case it will return 12, not 12.4. If you give the variable a value of -7.89 then SQL will return -7.

Cursor Variables

SQL Server supports cursor variables primarily to provide backward compatibility with batches, scripts, and programmable objects written for earlier SQL Server versions. The following is an example of creating a cursor variable:

DECLARE @cur_variable1 CURSOR;

Table Variables

Table variables behave in the same manner as local variables. A table variable stores the data in the form of a table. They are suitable for smaller data sets (typically less than 1,000 rows). The following is a basic example of creating a table variable:

DECLARE @Table1 TABLE
    (
    COL1 [int],
    COL2 [varchar](30)
    );

Table variables do not support FOREIGN KEY constraints. As an alternative to table variables you could use temporary tables.

Here is an article at Code Project that compares temporary tables and temporary variables. Here is an article at OdeToCode discussing Table variables.

Here are a few things to be aware of, as the article at Ode to Code points out. Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. Table variables store a set of records, the declaration syntax looks similar to a CREATE TABLE statement. We can use table variables in batches, stored procedures, and user-defined functions (UDFs). We can UPDATE records in our table variable as well as DELETE records. We can use constraints with table variables as well as primary keys. identity columns, and default values.

DECLARE @MyTableVariable TABLE
(
  TransactionID int,
  Description varchar(50)
)

Ode to Code says: “Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits – there will be no table to clean up with a DROP statement.”