From: esquel@sommarskog.se   
      
   Tony Mountifield (tony@softins.clara.co.uk) writes:   
   > I have scoured the online TSQL manual at MSDN and haven't found the   
   > answer to this question.   
   >   
   > In SQL Server 2000, how can I do MAX(a,b), GREATEST(a,b), MIN(a,b) or   
   > LEAST(a,b) without needing to evaluate both of the arguments twice?   
   >   
   > CASE WHEN a < b THEN b ELSE a END is not suitable, because one of the   
   > arguments is a sub-select.   
   >   
   > What I am trying to do is something like this:   
   >   
   > UPDATE foo SET x = MAX(0, x - (SELECT ......))   
   >   
   > or this:   
   >   
   > UPDATE foo SET x = x - MIN(x, (SELECT .....))   
   >   
   > in order to prevent x going negative.   
   >   
   > Any hints would be much appreciated!   
      
   I think Plamen's hope for the queries being evaluated only once is   
   in vain - SQL Server does not subquery matching.   
      
   I assume that the subqueries are correlated? In such case, you should   
   look for a way to use the UPDATE FROM syntax instead. However, depending   
   how the subselects relates to the query, this may not be possible on   
   SQL 2000.   
      
   The last resort would be materialise all to a table. and then update   
   from the table.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)