View source for Transact-SQL

{{short description|Microsoft's and Sybase's proprietary extension to Structured Query Language}}
'''Transact-SQL''' ('''T-SQL''') is [[Company:Microsoft|Microsoft]]'s and [[Company:Sybase|Sybase]]'s proprietary extension to the [[SQL]] (Structured Query Language) used to interact with [[Relational database|relational database]]s. T-SQL expands on the SQL standard to include [[Procedural programming|procedural programming]], [[Local variable|local variable]]s, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

Transact-SQL is central to using [[Software:Microsoft SQL Server|Microsoft 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 procedure]]s in 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: <code>DECLARE</code>, <code>SET</code> and <code>SELECT</code>.
<source lang="tsql">
DECLARE @var1 NVARCHAR(30);
SET @var1 = 'Some Name';
SELECT @var1 = Name
  FROM Sales.Store
  WHERE CustomerID = 100;
</source>

==Flow control==
Keywords for flow control in Transact-SQL include <code>BEGIN</code> and <code>END</code>, <code>BREAK</code>, <code>CONTINUE</code>, <code>GOTO</code>, <code>IF</code> and <code>ELSE</code>, <code>RETURN</code>, <code>WAITFOR</code>, and <code>WHILE</code>.

<code>IF</code> and <code>ELSE</code> 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 <code>@@DATEFIRST</code> setting.)

<source lang="tsql">
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
   PRINT 'It is the weekend.';
ELSE
   PRINT 'It is a weekday.';
</source>

<code>BEGIN</code> and <code>END</code> mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use <code>BEGIN</code> and <code>END</code> like this:

<source lang="tsql">
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(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;
</source>

<code>WAITFOR</code> 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.

<code>RETURN</code> is used to immediately return from a [[Stored procedure|stored procedure]] or function.

<code>BREAK</code> ends the enclosing <code>WHILE</code> loop, while <code>CONTINUE</code> causes the next iteration of the loop to execute. An example of a <code>WHILE</code> loop is given below.

<source lang="tsql">
DECLARE @i INT;
SET @i = 0;

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

==Changes to DELETE and UPDATE statements==
In Transact-SQL, both the <code>DELETE</code> and <code>UPDATE</code> statements are enhanced to enable data from another table to be used in the operation, without needing a subquery:
* <code>DELETE</code> accepts joined tables in the <code>FROM</code> clause, similarly to <code>SELECT</code>.  When this is done, the name or alias of which table in the join is to be deleted from is placed between <code>DELETE</code> and <code>FROM</code>.
* <code>UPDATE</code> allows a <code>FROM</code> clause to be added.  The table to be updated can be either joined in the <code>FROM</code> clause and referenced by alias, or referenced only at the start of the statement as per standard SQL.

This example deletes all {{code|users}} who have been flagged in the {{code|user_flags}} table with the 'idle' flag.
<source lang="tsql">
DELETE u
FROM users AS u INNER JOIN user_flags AS f ON u.id = f.id
WHERE f.name = 'idle';
</source>

==BULK INSERT==
<code>BULK</code> 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 of <code>BULK INSERT</code> results in better performance than processes that issue individual <code>INSERT</code> statements for each row to be added. Additional details are available [http://msdn2.microsoft.com/en-us/library/ms188365.aspx in MSDN].

==TRY CATCH==
Beginning with SQL Server 2005,<ref>[https://www.infoq.com/news/2012/03/T-SQL-2012 "T-SQL Improvements in SQL Server 2012"], Jonathan Allen on Mar 19, 2012, infoq.com</ref> Microsoft introduced additional <code>TRY CATCH</code> logic to support exception type behaviour.  This behaviour enables developers to simplify their code and leave out <code>@@ERROR</code> checking after each SQL execution statement.

<source lang="sql">
-- begin transaction
BEGIN TRAN;

BEGIN TRY
   -- execute each statement
   INSERT INTO MYTABLE(NAME) VALUES ('ABC');
   INSERT INTO MYTABLE(NAME) VALUES ('123');

   -- commit the transaction
   COMMIT TRAN;
END TRY
BEGIN CATCH
   -- roll back the transaction because of error
   ROLLBACK TRAN;
END CATCH;
</source>

==See also==
*[[Software:Adaptive Server Enterprise|Adaptive Server Enterprise (Sybase)]]
*PL/SQL (Oracle)
*PL/pgSQL (PostgreSQL)
*SQL/PSM (ISO standard)
*[[Tabular Data Stream]]

== References ==
{{reflist}}

==External links==
* [https://docs.microsoft.com/en-us/sql/t-sql/language-reference Transact-SQL Reference]
* [http://www.tsql.info Transact-SQL Tutorial]

[[Category:SQL]]
[[Category:Data-centric programming languages]]

{{Sourceattribution|Transact-SQL}}