The Transact-SQL (T-SQL) is a proprietary extension of Sybase and Microsoft to SQL . Transact-SQL was originally developed by Sybase as of early versions of its database engine of the same name. In a similar way to Oracle’s PL / SQL, Transact-SQL provided a way to extend the basic functionality of the DBMS, through programs called “stored procedures”. The source code, as compiled, is stored in the database, as opposed to programs written in a conventional programming language, the source code of which, on the other hand, the compiled code are stored in files File system.
When Microsoft wanted to extend its software offering to include a relational DBMS, it had agreements with one of the actors of the time, the challenger behind mainly Oracle, RTI Ingres, Informix: Sybase. The Sybase DBMS has been ported to Windows. Microsoft has gradually acquired the know-how in relational DBMS, and developed its own database engine, Microsoft SQL Server, from the Sybase strain. On the other hand, Sybase has evolved its DBMS, which has since become Adaptive Server Enterprise . Transact-SQL is now the programming language associated with both Microsoft SQL Server and Sybase Adaptive Server Enterprise .
Compared to SQL, T-SQL adds the following functionality:
- Elements of procedural programming ( loop , conditions …);
- The ability to create and use local variables ;
- String manipulation functions , dates and mathematical calculations.
The main key bits of procedural programming instructions are Transact-SQL
ELSEpermit conditional executions. The example below will show “We’re on weekends” if D-Day belongs to the weekend, “We’re on weekdays” for a day of the week.
IF DATEPART ( dw , GETDATE ()) = 6 OR DATEPART ( dw , GETDATE ()) = 7 PRINT 'We're on weekends.' ELSE PRINT 'We're in the week.'
ENDdelimit an instruction block. If more than one instruction is required in a conditional block, use BEGIN and END:
IF DATEPART ( dw , GETDATE ()) = 6 OR DATEPART ( dw , GETDATE ()) = 7 BEGIN PRINT 'We're on weekends.' PRINT 'Take a break!' END ELSE BEGIN PRINT 'We're in the week.' PRINT 'Quick to work!' END
WAITFOR Will allow to temporize the procedure, waiting for a certain time or a specific moment of the day.
RETURNIs used to terminate the execution of a stored procedure immediately or to return the result of a function.
BREAKWill terminate the block of a loop
CONTINUEforce the execution of a new iteration of the loop.
Transact-SQL allows the use of classical arithmetic operators.
- + ( Addition )
- – ( Subtraction )
- * ( Multiplication )
- / ( Division )
- % ( Modulo )
- = ( Equal to )
- > ( More than )
- <( Less than )
- > = ( Greater than or equal to )
- <= ( Less than or equal to )
- ! = ( Different from )
- <> ( Different from ), not standardized by ISO
- ! <( Not less than ), not standardized by ISO
- !> ( Not greater than ), not standardized by ISO
- Isnull ()
Local variables are often used as counters in loops
WHILEor type blocks
IF ... ELSE. They can be used in commands or stored procedures . The procedure uses these variables automatically and non-interactive when executed. Local variables can be used wherever the Transact-SQL syntax requires the use of an expression .
The declaration of local variables is similar to that of classical imperative languages. It follows the following syntax:
declare @ variable_name type_donn é es [, @ variable_name type_donn é es ] ...
Declare @ a int declare @ b char ( 20 ) declare @ c float
Declare @ a int , @ b char ( 20 ), @ c float
The second syntax is more efficient in terms of memory and performance.
Use in subroutines
A subroutine that assigns a value to a local variable must return only one value.
declare @ veryhigh money select @ veryhigh = max ( price ) from titles if @ veryhigh > $ 20 print "Ouch!"
declare @ one varchar ( 18 ), @ two varchar ( 18 ) select @ one = "a" , @ two = "two" if @ one = "a" print @ one if @ two = "two" print @ two
declare @ Tcount int , @ pcount int select @ Tcount = ( select count ( * ) from titles ) @ pcount = ( select count ( * ) from publishers ) select @ Tcount , @ pcount
Local variables are initially set to NULL when declared. They can also receive the NULL value of a SELECT command. Since NULL is a special value, the comparison between NULL and other NULL values must follow specific rules that are summarized in the table below.
An expression can be a variable , a constant , or a combination of both containing arithmetic operators .
|Comparison Type||Operator =||Operators <,>, <=,! =,! <,!>, <>|
|Comparing Two Columns with NULL Value||FALSE||FALSE|
|Comparison between NULL column and NULL expression||FALSE||FALSE|
|Comparing NULL and NULL Value Column||FALSE||FALSE|
|Comparing two NULL expressions||TRUE||FALSE|
DELETE and UPDATE
Transact-SQL allows you to add a clause
FROMto the operations
UPDATEallow joining tables.
The example below will erase from the table
utilisateurthose that have the ” Pending ” flag .
DELETE user FROM users have u JOIN drapeau_utilisateur as d ON u . Id = d . Id WHERE d . Name = 'Pending'