A While Loop with Min() or Max() Functions


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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net