Transact-SQL enhances SQL with these additional features:
ELSE allow conditional execution. This batch statement will print "weekend" if the current date is a weekend day, or "weekday" if the current date 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:
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're local to the script executing them. Transact SQL doesn't 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.
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:
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.
This example deletes all
users who have been flagged with the 'Idle' flag.