Mike,
Thanks, as this was particularly helpful.
tv
>As Bob commented, looping across all the rows in a table is a very row-oriented operation - one that SQL Server does not do very well. Yes, SQL Server does have this ability with server-side cursors, but there is a performance penalty associated with it.
>
>In general, you will want to create set-oriented solutions to your business issues. But don't be confused, a set may contain only one row if the situation calls for it. I like to tell people that if they think that they have to use a cursor, think harder <s>.
>
>You should also become familiar with the CASE expression. This is a very powerful feature of TSQL that will allow for very elegant set-orient solutions. For example, imagine that we want to increase the prices of products based on a scale:
>
>$0 - 100 +15%
>$101-1000 +10%
>$1001 and up + 5%
>
>You could create a cursor and iterate across the table. You could also create three UPDATE statements:
>
>UPDATE products SET price = price * 1.15 WHERE price BETWEEN 0 AND 100
>UPDATE products SET price = price * 1.10 WHERE price BETWEEN 101 AND 1000
>UPDATE products SET price = price * 1.05 WHERE price > 1000
>
>Or it can be done with a single query:
>
>UDPATE products
>SET price = price *
> CASE WHEN price BETWEEN 0 AND 100 THEN 1.15
> WHEN price BETWEEN 101 AND 1000 THEN 1.10
> WHEN price > 1000 THEN 1.05
> ELSE 1.0 END
>
>-Mike