Microsoft® SQL Server® 2012 Bible

402

Part IV: Programming with T-SQL

1. The WHILE command tests the condition. If the condition is true, WHILE executes
   the following command or block of code; if not, it skips the following command or
   block of code and moves on.


2. When the following command or block of code is complete, fl ow of control is
   returned to the WHILE command.
   The following short script demonstrates using the WHILE command to perform a loop:

DECLARE @Temp INT;

SET @Temp = 0;

WHILE @Temp < 3

BEGIN;

PRINT 'tested condition' + STR(@Temp);

SET @Temp = @Temp + 1;

END;

Result:

tested condition 0

tested condition 1

tested condition 2

The CONTINUE and BREAK commands enhance the WHILE command for more complex

loops. The CONTINUE command immediately jumps back to the WHILE command. The con-

dition is tested as normal.

The BREAK command immediately exits the loop and continues with the script as if the

WHILE condition was false. The following pseudocode (not intended to actually run) dem-

onstrates the BREAK command:

CREATE PROCEDURE MyLife()

AS

WHILE Not @@Eyes2blurry = 1

BEGIN;

EXEC Eat;

INSERT INTO Book(Words)

FROM Brain(Words)

WHERE Brain.Thoughts

IN('Make sense', 'Good Code', 'Best Practice');

IF @Firefly = 'On'

BREAK;

END;

Using Goto to Move to a Label

Before you associate the T-SQL GOTO command with bad memories of 1970s-style spaghetti-

BASIC, this GOTO command is limited to jumping to a label within the same batch or pro-

cedure and is rarely used for anything other than jumping to an error handler at the close

c16.indd 402c16.indd 402 7/30/2012 5:38:08 PM7/30/2012 5:38:08 PM

http://www.it-ebooks.info