Transact-SQL

 ( Sql ) 

Transact-SQL ( T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, , various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

Transact-SQL is central to using 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.

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

 FROM Sales.Store
 WHERE CustomerID = 100;
     

IF and ELSE 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.)

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1

  PRINT 'It is the weekend.';
     

  PRINT 'It is a weekday.';
     

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

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 BEGIN

  PRINT 'It is the weekend.';
  PRINT 'Get some rest on the weekend!';
     

  PRINT 'It is a weekday.';
  PRINT 'Get to work on a weekday!';
     

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 a stored procedure or function.

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

DECLARE @i INT; SET @i = 0;

WHILE @i < 5 BEGIN

  PRINT 'Hello world.';
  SET @i = @i + 1;
     

• DELETE accepts joined tables in the FROM clause, similarly to SELECT. When this is done, the name or alias of which table in the join is to be deleted from is placed between DELETE and FROM.
• UPDATE allows a FROM clause to be added. The table to be updated can be either joined in the FROM clause and referenced by alias, or referenced only at the start of the statement as per standard SQL.

This example deletes all who have been flagged in the table with the 'idle' flag. DELETE u FROM users AS u INNER JOIN user_flags AS f ON u.id = f.id WHERE f.name = 'idle';

-- 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;
     

  -- roll back the transaction because of error
  ROLLBACK TRAN;
     

• Adaptive Server Enterprise (Sybase)
• PL/SQL (Oracle)
• PL/pgSQL (PostgreSQL)
• SQL/PSM (ISO standard)
• Tabular Data Stream

• Transact-SQL Reference
• Transact-SQL Tutorial