A Brief Introduction to Transact-SQL - Looping Sequences
(Page 4 of 4 )
Looping Sequences
Loops are a critical part of programming languages. We use loops each time a specific segment or block of code needs to be executed multiple times. In this case, the WHILE loop executes the block of code until the condition is met (i.e. until the conditional expression returns true). The syntax is pretty straightforward: WHILE <statement> followed by the block of code delimited by a BEGIN…END structure.
DECLARE @MyNumber int
SET @MyNumber = 10
WHILE(@MyNumber>0)
BEGIN
PRINT ’MyNumber = ’ + CONVERT(nvarchar, @count)
SET @MyNumber = @MyNumber -1
END
The above example prints out the following sequence: “MyNumber = 10,” “MyNumber = 9,” “MyNumber = 8” … “MyNumber = 2,” and "MyNumber = 1.” We use MyNumber as an index for the WHILE loop. Its integer value gets decremented after each loop. Therefore, the loop works as long as it’s a larger number than zero.
We have a branch of other commands that we can use inside a WHILE loop. One of those powerful commands is CONTINUE. Simply put, it ignores the block of code (statements, etc.) that follows this "continue." It restarts the loop, moving to the next stage.
Another instruction that we shouldn’t forget about is the BREAK. This command exits the innermost WHILE loop. It skips the block of code and jumps directly to the END, which marks the termination of the loop. It’s really important that if more WHILE loops are nested, then BREAK exits the outermost loop, executing the statements that are after the inner loop’s end. And once they’re done, it restarts the outermost loop.
The RETURN instruction ends the execution without relying on any conditions, ignoring all of the statements following this keyword. You may add an integer at the end of this command if you want to pass on. The main thing you need to know is that RETURN is immediate and exits subroutines (stored procedures) and functions.
Usually if you do not specify a return value, then MS SQL Server returns one of the reserved values (-1 and -14). In general, 0 is considered successful completion of the stored procedure. So if you want to point out that a particular procedure succeeded, then type “RETURN 0” and be done with it. It’s more professional, safer, and clearer.
GOTO is an old-school code jump statement that does not get much love from experts, especially in high level programming languages. But it’s present in T-SQL, so let’s see how to use it. It works based on the following methodology: it defines a label and allows the programmer to instruct its code to jump to that label and continue with the execution. The label name is built by an identifier followed by a colon.
However, we need a little clarification: GOTO statements can be nested, but they can only be used within a statement block, batch, or stored procedure. This means that you cannot instruct a GOTO to jump to a label that’s located outside of the said batch.
A classic example of GOTO is used to replace a WHILE loop. Check it out!
DECLARE @count tinyint
SET @count = 0
MyLabel:
PRINT 'Developer Shed'
SET @count = @count + 1
IF @count <=4
GOTO MyLabel
The above code prints out "Developer Shed" 4 times. MyLabel is the label.
The last instruction that we’re going to cover in this article is WAITFOR. With this command we can instruct the program to delay the execution of instructions for a time period that we explicitly specify. Simply put, WAITFOR pauses the flow of execution until the specific time or time interval is reached.
There are two types of WAITFOR formats. We use the first if we want to specify the amount of time that the execution is going to be delayed. The syntax for this is: WAITFOR DELAY '00:00:10' --this results in a delay that’s 10 seconds long. However, should we need to specify an exact time, we use: WAITFOR TIME '05:00:00' - this results in the following segment executing at 5:00AM sharp.
Taking a Break
We’ve come to the end of this half. I truly hope that you found this article informative. T-SQL is by no means a hard language. So it’s more than enough to point out the specific functions, commands, instructions, etc., to explain what each of them does and how to implement them according to your needs. After you’ve developed familiarity, then you need to practice. Don’t forget that practice makes perfect.
We don’t have time to waste. In the next segment we are going to cover cursor-related programming techniques and instructions. We are going to learn how to use the FETCH command and how to implement proprietary variables such as @@FETCH_STATUS. Don’t be afraid. Like I said, T-SQL is a language that makes sense.
Furthermore we will spend our time delving into the world of functions. You can anticipate a thorough overview on the function categories. At first, we explore the vast amount of built-in functions and then move on to design the functions on our own.
Until then, keep coding!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 6 
