Transact-SQL

Transact-SQL(T-SQL) isMicrosoft's andSybase's proprietary extension to theSQL(Structured Query Language) used to interact withrelational databases.T-SQL expands on the SQL standard to includeprocedural programming,local variables,various support functions for string processing, date processing, mathematics, etc. and changes to theDELETEandUPDATEstatements.

Transact-SQL is central to usingMicrosoft SQL Server.All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

Stored proceduresin SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.

Variables

Transact-SQL provides the following statements to declare and set local variables:DECLARE,SETandSELECT.

DECLARE@var1NVARCHAR(30);
SET@var1='Some Name';
SELECT@var1=Name
FROMSales.Store
WHERECustomerID=100;

Flow control

Keywords for flow control in Transact-SQL includeBEGINandEND,BREAK,CONTINUE,GOTO,IFandELSE,RETURN,WAITFOR,andWHILE.

IFandELSEallow conditional execution. This batch statement will print "It is the weekend" if the current date is a weekend day, or "It is a weekday" if the current date is a weekday. (Note: This code assumes that Sunday is configured as the first day of the week in the@@DATEFIRSTsetting.)

IFDATEPART(dw,GETDATE())=7ORDATEPART(dw,GETDATE())=1
PRINT'It is the weekend.';
ELSE
PRINT'It is a weekday.';

BEGINandENDmark ablock of statements.If more than one statement is to be controlled by the conditional in the example above, we can useBEGINandENDlike this:

IFDATEPART(dw,GETDATE())=7ORDATEPART(dw,GETDATE())=1
BEGIN
PRINT'It is the weekend.';
PRINT'Get some rest on the weekend!';
END;
ELSE
BEGIN
PRINT'It is a weekday.';
PRINT'Get to work on a weekday!';
END;

WAITFORwill wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.

RETURNis used to immediately return from astored procedureor function.

BREAKends the enclosingWHILEloop, whileCONTINUEcauses the next iteration of the loop to execute. An example of aWHILEloop is given below.

DECLARE@iINT;
SET@i=0;

WHILE@i<5
BEGIN
PRINT'Hello world.';
SET@i=@i+1;
END;

Changes to DELETE and UPDATE statements

In Transact-SQL, both theDELETEandUPDATEstatements are enhanced to enable data from another table to be used in the operation, without needing a subquery:

DELETEaccepts joined tables in theFROMclause, similarly toSELECT.When this is done, the name or alias of which table in the join is to be deleted from is placed betweenDELETEandFROM.
UPDATEallows aFROMclause to be added. The table to be updated can be either joined in theFROMclause and referenced by alias, or referenced only at the start of the statement as per standard SQL.

This example deletes alluserswho have been flagged in theuser_flagstable with the 'idle' flag.

DELETEu
FROMusersASuINNERJOINuser_flagsASfONu.id=f.id
WHEREf.name='idle';

BULK INSERT

BULKis a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use ofBULK INSERTresults in better performance than processes that issue individualINSERTstatements for each row to be added. Additional details are availablein MSDN.

TRY CATCH

Beginning with SQL Server 2005,^[1]Microsoft introduced additionalTRY CATCHlogic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out@@ERRORchecking after each SQL execution statement.

-- begin transaction
BEGINTRAN;

BEGINTRY
-- execute each statement
INSERTINTOMYTABLE(NAME)VALUES('ABC');
INSERTINTOMYTABLE(NAME)VALUES('123');

-- commit the transaction
COMMITTRAN;
ENDTRY
BEGINCATCH
-- roll back the transaction because of error
ROLLBACKTRAN;
ENDCATCH;

References

^"T-SQL Improvements in SQL Server 2012",Jonathan Allen on Mar 19, 2012, infoq.com

External links

[1] "T-SQL Improvements in SQL Server 2012",Jonathan Allen on Mar 19, 2012, infoq.com

[1]