Variables and Parameters

Introduction

Variables are coding objects used in all programming languages. A variable is a temporary storage location in memory, given a name and assigned a value. The length of time a variable exists in memory varies by language and usage. In T-SQL, a variable exists only for the time it takes to run the script where it is defined. The variable does not exist before the script is run, or afterward.

Defining Variables

Most languages use some type of variable declaration statement, a way to name and reserve memory space for a variable, by data type. In T-SQL variables are defined using the DECLARE keyword, followed by one or more variable definitions. Multiple definitions are separated by commas. Each variable definition contains an "@" prefix, a name, and a datatype. For example:

DECLARE @days int, @saledate date, @totalamt smallmoney, @isvalid bit

Once a variable is declared, it can also be assigned or given a value. This sets the value of the variable so it can be used later in code. Variables in T-SQL can be assigned an initial value in the DECLARE statement. For example:

DECLARE @days int = 7, @saledate date = '2019-05-02', @maxamt smallmoney = 5000.00, @isvalid bit = 1;

It is also be possible to define the variables in a single line:

DECLARE @days int = 7, @saledate date = '2019-05-02', @maxamt smallmoney, @isvalid bit;

To assign a value to a variable after it is declared, the SET statement is used. For example:

SET @days = 4; SET @maxamt = 4500.00;

In testing, both the SELECT and PRINT statements can be used to display the value of a variable in Query Manager.

SET @days = 4; SET @maxamt = 4500.00; SELECT @days, @maxamt;

In this usage, SELECT statement does not need a table name, and will pull the values from memory.

Using Variables in Other Statements

A variable can take the place of any literal in a T-SQL statement. In the following examples, the variables take the place of literals in the WHERE clause:

DECLARE @days int = 7, @maxamt smallmoney; SELECT meetingname, roomsize from dbo.meetings WHERE numberofdays=@days; SELECT customername, creditlimit WHERE @maxamt > 4500.00;

Note that all the above statements must be executed at the same time. It is not possible to execute the DECLARE statements to declare the variables and then run the SELECT statements separately. Variables only exist when the script they are in is running. Once it finishes, the variables are destroyed and no longer exist.

Assigning Variable Values In a Query

Variables can also be assigned values directly from a SELECT statement:

SELECT @maxamt = creditlimit from dbo.customers WHERE customerid=5 ; SELECT @maxamt = MAX(creditlimit) from dbo.customers WHERE statecode='KY';

The above example demonstrates setting the value based on the results of a query. Note, however, that in each case, the query returns a single result. The second example uses an aggregate to return a single value for the largest credit limit. The query would result in an error if there was an attempt to asssign a value to a variable when the query returns multiple records.

If a query statement of this type fails (return zero records), then any variables would not be set and retain the current values. Take the following examples:

Variable Not Set Due to Query Failure

Figure 1: Variable Not Set Due to Query Failure

More than one variable can be assigned a value directly from a SELECT statement:

DECLARE @age int = 0, @dob date, @occupation varchar(40)=''; DECLARE @id int = 1001; SELECT @age = age, @dob=dateofbirth, @occupation=occupation from dbo.people WHERE personid=@id ;

Other Usage

Variables can be used in other ways, such as in calculations, or as parameter values in functions. Here is a longer example using the techniques discussed above:

DECLARE @currentcredit smallmoney=0; @newlimit smallmoney=0; @chgamt smallmoney=2000.00 SELECT @currentcredit = creditlimit from dbo.customers WHERE customerid=5 ; SET @newlimit = @currentcredit + @chgamt; UPDATE dbo.customers SET creditlimit = @newlimit WHERE customerid=5;

Again, note that the variables exist ONLY when the entire script is run. They are declared and exist starting with the first statement, and cease to exist when the script completes execution. The statements CANNOT be run one at a time, because the variables would not exist in any of the lines after the DECLARE statement.

Here is an example of a variable being used in a function call, with a query:

DECLARE @maxsize int=5; SELECT LEFT(zipcode, @maxsize) from dbo.addresses;

And another example with a function:

DECLARE @daysnet int=15; SELECT newduedate = DATEADD(dd,datebilled, @daysnet) from dbo.invoices;