Transact-SQL (T-SQL) is an extension of the SQL language from Microsoft. It’s used in SQL Server to program databases.
SCL Server includes many designs, components and functions that enhance the capabilities of the SQL language standard ANSI. Including classical programming which differs from the usual writing of requests.
Variables in T-SQL
These are needed in order to store some temporary data in which we need to keep for a while to use it later on during the process. There are two kinds of variables in T-SQL, these are local and global. Local variables exist only within the session during which they were created. Global variables are used to retrieve information about the SQL server or changing information within the database.
Local variables are declared using the DECLARE keyword and begin with the @ sign. As in many programming languages variables in T-SQL must have their own data type. There are a lot of data types in the SQL server and we examined them in detail in the previously mentioned reference book. You can use SET or Select to assign a value to a variable.
Global variables are needed in order to obtain any information about the server or the database, for example the global variables in SQL Server are:
- ROWCOUNT – stores the number of records processed by the previous command;
- ERROR – returns the error code for the last command;
- SERVERNAME – the name of the local SQL server;
- VERSION – version number of SQL Server;
- IDENTITY – the last value of the counter used in the insert operation.
Packages
A package in T-SQL is a command or SQL statement that is grouped together as the SQL server will compile and execute them as one. In order to make it clear to the SQL server that you are passing a command package you must specify the GO keyword after all the commands you want to merge into the package. Local variables will only be visible within the package in which they were created, i.e. You cannot access the variable after the completion of the package.
Conditional constructions
These constructions imply branching, i.e. depending on whether the specified conditions are met or not T-SQL instructions will change their direction.
IF … ELSE
This design is probably used in all programming languages it means checking the condition. If all the tests are completed then the command following is executed if not then nothing is done but you can specify the keyword ELSE. Then in this case the specified operators will be executed after this word.
IF EXISTS
This design allows you to determine whether there are certain conditions that are recorded. For example we want to know whether there are some records in the table and if the first match is found processing of the command is terminated. In fact, this is the same as COUNT (*)> 0.
CASE
This construct is used in conjunction with the select statement and is intended to replace the multiple use of the IF construct. It is useful in cases where it is necessary to check a variable (or field) for the presence of certain values.
BEGIN … END
This construction is necessary to create a command block, i.e. if we wanted to execute more than one command after the IF block and a few more then we would have to write all the commands inside the BEGIN … END block.
T-SQL Loops
Generally speaking in regards to cycles they are needed for repeated execution of commands. In the T-SQL language there is one WHILE loop with a precondition which means that the commands will begin and will be repeated until the condition is fulfilled before the start of the loop. The loop can also be controlled using the BREAK and CONTINUE keywords.
Comments
Are needed in order to explain it take notes in the code because if the code is large and complex you can simply forget why you wrote this or that piece of code. In the T-SQL language there are single-line (–Text) and multi-line comments (/ * Text * /).
T-SQL Commands
GOTO
With this command you can navigate the code to the specified label. For example, you can use it when you want to do a kind of loop but without while.
WAITFOR
This command can suspend code execution for a while or until the specified time. The DELAY parameter pauses the specified length and TIME pauses the process until the specified time is reached. The parameter value is specified in the format hh: mi: from
RETURN
This command is used to exit the query or procedure unconditionally. RETURN can be used at any point to exit the procedure, package or instruction block. All that goes after this command is not executed.
To send a service message you can use the PRINT command. In Management Studio this message appears on the Messages tab.
Transactions
A transaction is a command or a block of commands that are successfully completed or canceled as a whole. In other words, if one command or instruction within the transaction has failed all that has been worked on is also canceled even if the previous commands were successful.
Error handling – TRY … CATCH construction
During the execution of T-SQL code an unexpected situation may occur. The error to be processed. In SQL Server starting since 2005 there is such a design as TRY … CATCH which can track the error.
In this example, a situation has arisen that there is a division by zero (as you know, you cannot divide by 0) and since our code block was placed in the TRY construct we had an exception in which we simply get the error number and its description.
