From Seo Wiki - Search Engine Optimization and Programming Languages
|The introduction to this article provides insufficient context for those unfamiliar with the subject. Please help improve the article with a good introductory style. (October 2009)|
Transact-SQL augments SQL with certain additional features:
- Control-of-flow language
- Local variables
- Various support functions for string processing, date processing, mathematics, etc.
- Changes to DELETE and UPDATE statements
Keywords for flow control in Transact-SQL include
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.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT 'It is the weekend.' ELSE PRINT 'It is a weekday.'
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!' END ELSE BEGIN PRINT 'It is a weekday.' PRINT 'Get to work!' 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 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.
Local variables are so named because they are local to the script executing them. Transact SQL does not support user-defined global variables.
DECLARE will declare a variable, giving it a name and a type. The SET statement can be used to provide a value, and the variable may be used in a statement by referencing its name.
This script declares a variable as an integer, initializes it, then uses
WHILE to execute a loop.
DECLARE @Counter INT SET @Counter = 10 WHILE @Counter > 0 BEGIN PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter) SET @Counter = @Counter - 1 END
The body of the loop will print a message including the value of the variable, and then decrement the counter.
A variable can be initialized as the result of a statement, like this:
DECLARE @ArticleCount INT SELECT @ArticleCount = COUNT(*) FROM Articles INSERT INTO SizeLog (SampleTime, ArticleCount) VALUES (GETDATE(), @ArticleCount)
which will get the count of rows in the Articles table, then insert a row including that count and the current clock time into the SizeLog table.
Changes to DELETE and UPDATE statements
In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included.
This example deletes all
users who have been flagged with the 'Idle' flag.
DELETE users FROM users as u JOIN user_flags as f ON u.id=f.id WHERE f.name = 'Idle'
BULK INSERT 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 BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added. Additional details are available on Microsoft's MSDN page.
- Sybase Transact-SQL User's Guide
- Transact-SQL Reference for SQL Server 2000 (MSDN)
- Transact-SQL Reference for SQL Server 2005 (MSDN)
- Transact-SQL Reference for SQL Server 2008 (MSDN)cs:Transact-SQL