Transact-SQL


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.

Procedural programming

The main key bits of procedural programming instructions are Transact-SQL BEGINand END, BREAK, CONTINUE, GOTO, IFand ELSE, RETURN, WAITFOR, and WHILE.

IFAnd 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.'

BEGINAnd 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 WHILE, while CONTINUEforce the execution of a new iteration of the loop.

Operators

Arithmetic operators

Transact-SQL allows the use of classical arithmetic operators.

  • + ( Addition )
  • – ( Subtraction )
  • * ( Multiplication )
  • / ( Division )
  •  % ( Modulo )

Comparison operators

  • = ( 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

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 .

Declaration

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 ] ...

Examples:

Declare @ a int
declare @ b char ( 20 )
declare @ c float

OR

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.

Examples:

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

NULL value

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 .

Comparisons between NULL values
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 DELETEand 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'