Local Variables and while Loops and if…else Blocks

You can use local variables in a counter in a while loop, for performing matching in a where clause and in an if statement, and for setting and resetting values in select statements.

/* Determine if a given au_id has a row in au_pix*/
/* Turn off result counting */
set nocount on
/* declare the variables */
declare @c int,
        @min_id varchar(30)
/*First, count the rows*/
select @c = count(*) from authors 
/* Initialize @min_id to "" */
select @min_id = "" 
/* while loop executes once for each authors row */
while @c > 0
        /*Find the smallest au_id*/
        select @min_id = min(au_id)
            from authors
            where au_id > @min_id
        /*Is there a match in au_pix?*/
        if exists (select au_id
            from au_pix
            where au_id = @min_id)
            print "A Match! %1!", @min_id
      select @c = @c -1  /*decrement the counter */