Transact-SQL: Difference between revisions
added the intro from: http://msdn.microsoft.com/en-us/library/ms189826(SQL.90).aspx |
Added WHILE code snippet |
||
| Line 42: | Line 42: | ||
<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. |
<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. |
||
<code>declare @i nvarchar(50) |
|||
set @i = 0 |
|||
while @i < 5 |
|||
begin |
|||
print 'hello world' |
|||
set @i = @i + 1 |
|||
end</code> |
|||
==Changes to DELETE and UPDATE statements== |
==Changes to DELETE and UPDATE statements== |
||
Revision as of 22:55, 5 August 2010
 | This article provides insufficient context for those unfamiliar with the subject. (October 2009) |
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Transact-SQL is central to using 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.
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
These additional features make Transact-SQL Turing complete.
Flow control
Keywords for flow control in Transact-SQL include BEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE.
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.
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
PRINT 'It is the weekend.'
ELSE
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!'
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.
declare @i nvarchar(50)
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 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
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.