It is possible to iterate through a table or recordset using a While statement with the aggregate() function, which returns extreme values: Min() and Max(). Take a look at the following batch:
declare Value int -- get first value Select @Value = MIN(Value) From aTable -- loop While @Value is not null Begin -- do something instead of just displaying a value Select @Value value -- get next value Select @Value = MIN(Value) From aTable Where Value > @Value End
The first Select statement with the Min() function obtains a first value from the set (table):
Select @Value = MIN(Value) From aTable
The next value is obtained in a loop as a minimal value bigger than the previous one:
Select @Value = MIN(Value) From aTable Where Value > @Value
If no records qualify as members of the set, an aggregate() function will return null. You can then use null as a criterion to exit the loop:
While @Value is not null
To demonstrate this method, the following rewrites prSpaceUsedByTables, which displays the space used by each user-defined table in the current database:
Create Procedure util.ap_SpaceUsedByTables_4 -- loop through table names in current database -- display info about amount of space used by each table -- demonstration of while loop As Set nocount on Declare OTableName sysname -- get first table name Select @TableName = Min(name) From sys.sysobjects Where xtype = 'U' While @TableName is not null Begin -- display space used Exec sp_spaceused @TableName -- get next table Select @TableName = Min(name) From sysobjects Where xtype = 'U' And name > @TableName End Return 0
This was just an academic example. Naturally, the proper solution includes a temporary table to collect all results and display them at the end in one recordset. Note that I am not talking about a temporary table such as the one used in Chapter 3 for looping using a While statement.
You can step backward through the recordset if you use the Max() function and if you compare the old record and the remainder of the set using the < operator.
Tip | This method may he a quick solution for problems that require iteration. However, solutions hased on set operations usually provide superior performance. |