Today, almost every modern programmer knows what Transact-SQL is. This is the extension that is used in SQL Server. This development is tightly integrated into the Microsoft SQL language and adds programming constructors that are not initially provided in the databases. T-SQL supports variables, as in most other designs. However, this extension restricts the use of variables in ways that are not common in other environments.
Declaring Variables in DECLARE SQL
T-SQL uses the DECLARE statement (<var_name>) to declare a variable. For example, in the case of declaring the variable i as an integer using this operator, the command would look like this: DECLARE @i int.
Although Microsoft does not document this function, T-SQL also supports specifying the AS keyword between the variable name and its data type, as in the following example: DECLARE @i AS int. The AS keyword makes reading DECLARE statements easier. The only data type that the AS keyword does not allow is the table data type, which is new in SQL Server 2000. It allows you to define a variable that contains the entire table.
DECLARE SQL: Description
T-SQL only supports local variables that are available exclusively in the batch that created them. A package is an operator (or group of operators) that the database parses as a unit. Each client tool or interface has its own way of indicating where the package ends. For example, in Query Analyzer, you use the GO command to indicate where the package ends. If you have a syntax error in any statement, the package does not go through the parsing phase, so the client tool does not send the package to SQL Server for further processing. You can run code that declares a table variable and then inserts a row into the table in the same batch.
SQL Declare Table example:
DECLARE @mytable table
col1 int NOT NULL
INSERT INTO @mytable VALUES (1)
GO
Now declare the table variable in one batch, and then insert the row into the table in another batch:
DECLARE @mytable table
col1 int NOT NULL
INSERT INTO @mytable VALUES (1) GO
The INSERT statement fails because the table variable is out of scope and the following error message appears:
Server: msg 137, level 15, state 2, line 2.
Variables in procedures (DECLARE, SET instructions)
Support for local variables in SQL procedures allows you to assign and retrieve data values in support of procedure logic. Variables in procedures are defined using the DECLARE SQL statement. Values can be assigned to variables using the SET statement or as the default value when declaring a variable. Literals, expressions, query results, and special register values can be assigned variables.
Variable values can be assigned to procedure parameters, other variables, and can also be specified as parameters in SQL statements executed within the procedure.
Algorithm
When declaring a variable, you can specify a default value using the DEFAULT clause. The string shows a declaration of a variable of type Boolean with a default value of FALSE. The SET statement can be used to assign a single value to a variable. Variables can also be set by executing a SELECT or FETCH statement in conjunction with an INTO clause. The VALUES INTO statement can be used to evaluate a function or a special register and assign a value to several variables.
You can also assign the result of the GET DIAGNOSTICS statement to a variable. GET DIAGNOSTICS can be used to get a handle to the number of rows affected (updated for an UPDATE statement, DELETE for a DELETE statement) or return status of a SQL statement just executed
Features
The DECLARE SQL line demonstrates how part of the logic can be used to determine the value to be assigned to a variable. In this case, if the rows were changed as part of an earlier DELETE statement, and the execution of GET DIAGNOSTICS caused the v_rcount variable to be set to a value greater than zero, the is_done variable is set to TRUE.
Procedures
DECLARE SQL procedures are procedures that are fully implemented using SQL and can be used to encapsulate logic. The same, in turn, can be called as a programming routine.
There are many useful SQL procedure applications in the database architecture . They are used to create simple scripts to quickly query for data conversion and updating, generate basic reports, increase productivity and modulate applications, and also improve overall database design and security.
There are many functions of procedures that make them a powerful processing tool. Before deciding whether to implement the SQL procedure, it is important to understand what analogues are in the context of routines, how they are implemented, and how they can be used.
Creating Procedures
Implementing SQL procedures can play an important role in database architecture, application development, and system performance. Development requires a clear understanding of the requirements, capabilities and use of functions, as well as knowledge of any restrictions. SQL procedures are created using the CREATE PROCEDURE statement. When an algorithm is created, the requests in the body of the procedure are separated from the procedural logic. To maximize performance, SQL queries are statically compiled into sections in a package
Variables
A Transact-SQL local variable is an object that can contain a single data value of a particular type. Variables are commonly used in batches and scenarios:
- as a counter, you must either count the number of cycles, or set how many times the cycle is performed;
- To save the data value that should be verified by the flow control operator
- to store the data value that will be returned by the return function code.
The names of a number of Transact-SQL functions begin with the characters (@@). Although in earlier versions of Microsoft SQL Server, @@ functions are called global variables. @@ are system functions, and their use is subject to syntax rules for functions.
Variable declaration
The DECLARE statement defines a Transact-SQL variable according to the following algorithm:
- defining a name that should have one @ character as the first character;
- assignment of a data type or length specified or user-defined;
- numerical variables are also assigned precision and scale.
- XML type variables can be assigned an additional schema assembly.
- Setting the value to NULL. For example, the DECLARE statement in an SQL query creates a local variable named @mycounter with an int data type.
To declare multiple local variables, use a comma after defining the first local variable, and then specify the following local network name and data type. For example, the following statement creates three local variables named @LastName, @FirstName and @StateProvince and initializes each to NULL. The scope of a variable is the range of Transact-SQL statements that can reference a variable. The volume of a variable lasts from the point that is declared until the end of the batch or stored procedure at which it is declared.