A Brief Introduction to Transact-SQL - Control of Flow Mechanisms
(Page 3 of 4 )
Control-of-Flow Mechanisms
As I mentioned at the beginning of my article, one of the most outstanding enhancements that T-SQL brought to the traditional de facto standard SQL was the addition of the control-of-flow mechanism. Here we can enroll commands such as IF…END, BEGIN…END, BREAK, GOTO, CONTINUE, RETURN, WAITFOR, and WHILE.
The way we use conditional statements hasn’t changed. However, thanks to the T-SQL enhancement we can add a block of code into IF statements. This also means that we can nest more than one IF statement. An IF conditional works based on the following methodology: it executes the followed statements if and only if the testing is true. When the condition isn’t met, the ELSE part is executed (if there is one).
For each time you want to add a block of code, don’t forget to delimit these using the BEGIN and END commands. BEGIN…END groups the commands in between. Check out the following example.
IF condition1
BEGIN
lots of commands
END
ELSE
IF condition2
BEGIN
another pack of commands
END
ELSE
BEGIN
yet another batch of commands
END
I hope that the above example is intuitive. Here it is in plain English: if condition1 is met, then the lots of commands part is executed; if condition1 isn’t met, then the second condition2 is tested; if it’s validated as true, then another pack of commands is executed; however, if this also returns a false validation, then, obviously, yet another batch of commands is executed, since there aren’t any more conditions.
Makes sense, doesn’t it? Great! Now let’s see how the CASE commands act.
CASE is a command that handles a list of conditionals and returns the execution of one of them. There are two formats in which you can use the CASE command. The first one is the simple CASE that works on the basis of comparison. Once the condition is met, the following expressions are returned. The second type is the searched CASE that evaluates a list of Boolean expressions in order to determine the results.
Check out the following “simple CASE” example. Can you guess what it does?
DECLARE @stateshort nchar(2)
DECLARE @statelong nchar(8)
SET @stateshort = ’DS’
SELECT @statelong =
CASE @stateshort
WHEN ’SC’ THEN ’SEO Chat’
WHEN ’DS’ THEN ’Dev Shed’
WHEN ’AF’ THEN ’SP Free’
END
Yes, that’s right! Indeed, the content of the stateshort variable is evaluated, which is "DS," and it is met on the second conditional line. As a result the statelong variable ends up receiving the "Dev Shed" value. Notice that we’ve set the variable statelong after SELECT, so we can “work with it” once one of the conditions are met.
Next: Looping Sequences >>
More MS SQL Server Articles
More By Barzan "Tony" Antal
/ 6 
