Language elements
This section will be dedicated to explaining all the essential elements to handle encoding multiple Transact-SQL statements within a block of code. We will see, among other things: the use of variables, the specification of the type of calculation through operators and the most important flow control structures
-
Variables in Transact SQL
Variables are objects that store values to retrieve and use in other statements. They are often used in control flow structures, stored procedures, cursors, scripts, and batches.
-
Operators in SQL
The operators are symbols that specify the type of calculation that the different expressions perform. We will review the different equality and conditional SQL operators
Runnable examples
-
BEGIN END statement in Transact SQL
BEGIN END are flow control structures to contain several statements grouped in a given code block. In short, they allow a series of instructions to be executed together
-
IF...ELSE conditional statement in Transact SQL
IF...ELSE are flow control structures to validate conditions in the instructions of one or more statements
-
WHILE statement in Transact SQL
WHILE is a flow control structure that allows statements to be executed in a loop as long as the condition it validates is 'TRUE'
-
Error handling in Transact SQL
Error handling is a fundamental aspect in the management of Transact-SQL code, which allows us to control our code flow and detect problems and errors to facilitate their resolution or information
-
Transactions in Transact SQL
A transaction can be defined as a set of instructions that work as a unit, where if one part fails, the whole process fails in its entirety, reverting all the changes produced in the database
-
CURSOR in Transact SQL
A cursor is a data structure that allows you to go through the set of rows returned by a SELECT and process each record one by one
What you will learn in this section
So far each statement ran on its own. Transact-SQL also lets you write small programs: store values, make decisions, repeat operations and handle errors. This is what stored procedures, functions and triggers are built on.
- Variables and operators:
DECLARE,SET, and comparison and logical operators, plusLIKE,INandBETWEEN. - Control flow:
BEGIN...END,IF...ELSEandWHILE. - Error handling with
TRY...CATCH. - Transactions: grouping operations so that either all of them apply or none does.
- Cursors: walking through a result row by row.
The operators lesson has runnable examples, because those operators work the same in any database. The rest of the section is specific to SQL Server.