The following example uses local variables in a counter in a while loop, for doing matching in a where clause, 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
begin
/*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)
begin
print "A Match! %1!", @min_id
end
select @c = @c -1 /*decrement the counter */
end
