Sql Server Read Text File Into Variable
Summary: in this tutorial, you will learn about variables including declaring variables, setting their values, and assigning value fields of a tape to variables.
What is a variable
A variable is an object that holds a unmarried value of a specific type eastward.1000., integer, date, or varying character string.
We typically use variables in the post-obit cases:
- Every bit a loop counter to count the number of times a loop is performed.
- To hold a value to be tested by a command-of-flow argument such every bit
WHILE. - To store the value returned by a stored procedure or a role
Declaring a variable
To declare a variable, you lot utilise the DECLARE argument. For instance, the following argument declares a variable named @model_year:
DECLARE @model_year SMALLINT;
Lawmaking language: SQL (Structured Query Language) ( sql ) The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must showtime with the @ sign. In this example, the information type of the @model_year variable is SMALLINT.
By default, when a variable is declared, its value is set to Nil.
Between the variable name and data type, you can use the optional AS keyword every bit follows:
DECLARE @model_year Every bit SMALLINT;
Code language: SQL (Structured Query Language) ( sql ) To declare multiple variables, you split variables past commas:
DECLARE @model_year SMALLINT, @product_name VARCHAR(MAX);
Lawmaking language: SQL (Structured Query Language) ( sql ) Assigning a value to a variable
To assign a value to a variable, you lot use the Gear up statement. For example, the following statement assigns 2018 to the @model_year variable:
SET @model_year = 2018;
Code language: SQL (Structured Query Language) ( sql ) 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 yr:
SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year ORDER BY product_name;
Lawmaking linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) At present, you can put everything together and execute the following lawmaking block to get a list of products whose model year is 2018:
DECLARE @model_year SMALLINT; SET @model_year = 2018; SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year ORDER Past product_name;
Code language: SQL (Structured Query Language) ( sql ) Note that to execute the code, you click the Execute button as shown in the following picture:
The following pic shows the output:
Storing query upshot in a variable
The following steps describe how to store the query consequence in a variable:
First, declare a variable named @product_count with the integer information type:
DECLARE @product_count INT;
Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) Second, use the Fix statement to assign the query'south result fix to the variable:
SET @product_count = ( SELECT COUNT(*) FROM production.products );
Code language: SQL (Structured Query Language) ( sql ) Third, output the content of the @product_count variable:
SELECT @product_count;
Code language: SQL (Structured Query Linguistic communication) ( sql ) Or you lot can utilise the PRINT statement to print out the content of a variable:
Code linguistic communication: 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 Every bit VARCHAR(MAX));
The output in the messages tab is as follows:
Lawmaking linguistic communication: SQL (Structured Query Language) ( sql )
The number of products is 204
To hibernate the number of rows affected messages, you utilise the following argument:
Fix NOCOUNT ON;
Code language: SQL (Structured Query Language) ( sql ) Selecting a tape into variables
The post-obit steps illustrate how to declare ii variables, assign a record to them, and output the contents of the variables:
Start, declare variables that agree the product name and list price:
DECLARE @product_name VARCHAR(MAX), @list_price DECIMAL(x,2);
Code language: SQL (Structured Query Linguistic communication) ( sql ) 2d, assign the cavalcade names to the respective variables:
SELECT @product_name = product_name, @list_price = list_price FROM production.products WHERE product_id = 100;
Lawmaking language: SQL (Structured Query Language) ( sql ) 3rd, output the content of the variables:
SELECT @product_name As product_name, @list_price AS list_price;
Code language: SQL (Structured Query Language) ( sql ) 
Accumulating values into a variable
The following stored procedure takes ane parameter and returns a listing of products as a string:
CREATE PROC uspGetProductList( @model_year SMALLINT ) Equally Brainstorm DECLARE @product_list VARCHAR(MAX); Prepare @product_list = ''; SELECT @product_list = @product_list + product_name + CHAR(10) FROM production.products WHERE model_year = @model_year ORDER BY product_name; Impress @product_list; Stop;
Code language: SQL (Structured Query Language) ( sql ) In this stored process:
- First, nosotros declared a variable named
@product_listwith varying character string type and set its value to blank. - 2nd, we selected the production name listing from the products table based on the input
@model_year. In the select list, we accumulated the product names to the@product_listvariable. Notation that theCHAR(x)returns the line feed graphic symbol. - 3rd, we used the
Impressargument to print out the product list.
The post-obit statement executes the uspGetProductList stored procedure:
Code linguistic communication: SQL (Structured Query Language) ( sql )
EXEC uspGetProductList 2018
The following moving picture shows the partial output:
In this tutorial, yous have learned virtually variables including declaring variables, setting their values, and assigning value fields of a tape to the variables.
Source: https://www.sqlservertutorial.net/sql-server-stored-procedures/variables/
0 Response to "Sql Server Read Text File Into Variable"
Postar um comentário