 | This article has multiple issues. Please helpimprove it or discuss these issues on thetalk page.(Learn how and when to remove these messages) (Learn how and when to remove this message)
|
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 theDELETE andUPDATE statements.
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 procedures in SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.
Transact-SQL provides the following statements to declare and set local variables:DECLARE,SET andSELECT.
DECLARE@var1NVARCHAR(30);SET@var1='Some Name';SELECT@var1=NameFROMSales.StoreWHERECustomerID=100;
Keywords for flow control in Transact-SQL includeBEGIN andEND,BREAK,CONTINUE,GOTO,IF andELSE,RETURN,WAITFOR, andWHILE.
IF andELSE allow 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@@DATEFIRST setting.)
IFDATEPART(dw,GETDATE())=7ORDATEPART(dw,GETDATE())=1PRINT'It is the weekend.';ELSEPRINT'It is a weekday.';
BEGIN andEND mark ablock of statements. If more than one statement is to be controlled by the conditional in the example above, we can useBEGIN andEND like this:
IFDATEPART(dw,GETDATE())=7ORDATEPART(dw,GETDATE())=1BEGINPRINT'It is the weekend.';PRINT'Get some rest on the weekend!';END;ELSEBEGINPRINT'It is a weekday.';PRINT'Get to work on a weekday!';END;
WAITFOR will 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.
RETURN is used to immediately return from astored procedure or function.
BREAK ends the enclosingWHILE loop, whileCONTINUE causes the next iteration of the loop to execute. An example of aWHILE loop is given below.
DECLARE@iINT;SET@i=0;WHILE@i<5BEGINPRINT'Hello world.';SET@i=@i+1;END;
In Transact-SQL, both theDELETE andUPDATE statements are enhanced to enable data from another table to be used in the operation, without needing a subquery:
DELETE accepts joined tables in theFROM clause, similarly toSELECT. When this is done, the name or alias of which table in the join is to be deleted from is placed betweenDELETE andFROM.UPDATE allows aFROM clause to be added. The table to be updated can be either joined in theFROM clause and referenced by alias, or referenced only at the start of the statement as per standard SQL.This example deletes allusers who have been flagged in theuser_flags table with the 'idle' flag.
DELETEuFROMusersASuINNERJOINuser_flagsASfONu.id=f.idWHEREf.name='idle';
BULK is 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 INSERT results in better performance than processes that issue individualINSERT statements for each row to be added. Additional details are availablein MSDN.
Beginning with SQL Server 2005,[1] Microsoft introduced additionalTRY CATCH logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out@@ERROR checking after each SQL execution statement.
-- begin transactionBEGINTRAN;BEGINTRY-- execute each statementINSERTINTOMYTABLE(NAME)VALUES('ABC');INSERTINTOMYTABLE(NAME)VALUES('123');-- commit the transactionCOMMITTRAN;ENDTRYBEGINCATCH-- roll back the transaction because of errorROLLBACKTRAN;ENDCATCH;