Examples Using Parameters in Nsql
Summary: in this tutorial, you will learn about variables including declaring variables, setting their values, and assigning value fields of a record to 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:
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
DECLARE @model_year AS SMALLINT;
To declare multiple variables, you separate variables by commas:
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year ORDER 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:
Code language: SQL (Structured Query Language) ( sql )
DECLARE @model_year SMALLINT; SET @model_year = 2018; SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year ORDER 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:
Code language: SQL (Structured Query Language) ( sql )
DECLARE @product_count INT;
Second, use the SET statement to assign the query's result set to the variable:
Code language: SQL (Structured Query Language) ( sql )
SET @product_count = ( SELECT COUNT(*) FROM production.products );
Third, output the content of the @product_count variable:
Code language: SQL (Structured Query Language) ( sql )
SELECT @product_count;
Or you can use the PRINT statement to print out the content of a variable:
Code language: SQL (Structured Query Language) ( sql )
PRINT @product_count;
or
Code language: SQL (Structured Query Language) ( sql )
PRINT 'The number of products is ' + CAST(@product_count AS VARCHAR(MAX));
The output in the messages tab is as follows:
Code language: SQL (Structured Query Language) ( sql )
The number of products is 204
To hide the number of rows affected messages, you use the following statement:
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
DECLARE @product_name VARCHAR(MAX), @list_price DECIMAL(10,2);
Second, assign the column names to the corresponding variables:
Code language: SQL (Structured Query Language) ( sql )
SELECT @product_name = product_name, @list_price = list_price FROM production.products WHERE product_id = 100;
Third, output the content of the variables:
Code language: SQL (Structured Query Language) ( sql )
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:
Code language: SQL (Structured Query Language) ( sql )
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_listwith 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_listvariable. Note that theCHAR(10)returns the line feed character. - Third, we used the
PRINTstatement to print out the product list.
The following statement executes the uspGetProductList stored procedure:
Code language: SQL (Structured Query Language) ( sql )
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.
Examples Using Parameters in Nsql
Source: https://www.sqlservertutorial.net/sql-server-stored-procedures/variables/
0 Response to "Examples Using Parameters in Nsql"
Post a Comment