Variables

What is a variable

A variable is an object that holds a single value of a specific type e.g., integer, date, or varying character string.

We typically use variables in the following cases:

• As a loop counter to count the number of times a loop is performed.
• To hold a value to be tested by a control-of-flow statement such as WHILE.
• To store the value returned by a stored procedure or a function

Declaring a variable

To declare a variable, you use the DECLARE statement. For example, the following statement declares a variable named @model_year:

1	DECLARE @model_year SMALLINT;

The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must start with the @ sign. In this example, the data type of the @model_year variable is SMALLINT.

By default, when a variable is declared, its value is set to NULL.

Between the variable name and data type, you can use the optional AS keyword as follows:

1	DECLARE @model_year AS SMALLINT;

To declare multiple variables, you separate variables by commas:

12	DECLARE @model_year SMALLINT,         @product_name VARCHAR(MAX);

Assigning a value to a variable

To assign a value to a variable, you use the SET statement. For example, the following statement assigns 2018 to the @model_year variable:

1	SET @model_year = 2018;

Using variables in a query

The following SELECT statement uses the @model_year variable in the WHERE clause to find the products of a specific model year:

12345678910

SELECT    product_name,    model_year,    list_price FROM     production.productsWHERE     model_year = @model_yearORDER BY    product_name;

Now, you can put everything together and execute the following code block to get a list of products whose model year is 2018:

1234567891011121314

DECLARE @model_year SMALLINT; SET @model_year = 2018; SELECT    product_name,    model_year,    list_price FROM     production.productsWHERE     model_year = @model_yearORDER BY    product_name;

Note that to execute the code, you click the Execute button as shown in the following picture:

The following picture shows the output:

Storing query result in a variable

The following steps describe how to store the query result in a variable:

First, declare a variable named @product_count with the integer data type:

1	DECLARE @product_count INT;

Second, use the SET statement to assign the query’s result set to the variable:

123456

SET @product_count = (    SELECT         COUNT(*)     FROM         production.products );

Third, output the content of the @product_count variable:

1	SELECT @product_count;

Or you can use the PRINT statement to print out the content of a variable:

1	PRINT @product_count;

or

1	PRINT ‘The number of products is ‘ + CAST(@product_count AS VARCHAR(MAX));

The output in the messages tab is as follows:

1	The number of products is 204

To hide the number of rows affected messages, you use the following statement:

1	SET NOCOUNT ON;

Selecting a record into variables

The following steps illustrate how to declare two variables, assign a record to them, and output the contents of the variables:

First, declare variables that hold the product name and list price:

123	DECLARE     @product_name VARCHAR(MAX),    @list_price DECIMAL(10,2);

Second, assign the column names to the corresponding variables:

1234567

SELECT     @product_name = product_name,    @list_price = list_priceFROM    production.productsWHERE    product_id = 100;

Third, output the content of the variables:

123	SELECT     @product_name AS product_name,     @list_price AS list_price;

Accumulating values into a variable

The following stored procedure takes one parameter and returns a list of products as a string:

1234567891011121314151617181920

CREATE  PROC uspGetProductList(    @model_year SMALLINT) AS BEGIN    DECLARE @product_list VARCHAR(MAX);     SET @product_list = ”;     SELECT        @product_list = @product_list + product_name                         + CHAR(10)    FROM         production.products    WHERE        model_year = @model_year    ORDER BY         product_name;     PRINT @product_list;END;

In this stored procedure:

• First, we declared a variable named @product_list with varying character string type and set its value to blank.
• Second, we selected the product name list from the products table based on the input @model_year. In the select list, we accumulated the product names to the @product_list variable. Note that the CHAR(10) returns the line feed character.
• Third, we used the PRINT statement to print out the product list.

The following statement executes the uspGetProductList stored procedure:

1	EXEC uspGetProductList 2018

The following picture shows the partial output:

In this tutorial, you have learned about variables including declaring variables, setting their values, and assigning value fields of a record to the variables.