[dba-SQLServer] If or Case or 'what'?

On 20 Jun 2006 at 16:13, Mark A Matte wrote:

> The guy I'm helping with this...just shared exactly what he is trying to
> do...so the next question is...is there a limit to the number of cases you
> can have in a single statement?
> 
Not as far as I know.   

> ...or is there a better approach to what we are doing?  We have a table with
> all of the data created below(my first email)...then he wants to populate a
> category field with 1 of about 30 values...depending on the data in the
> records.  Then there is another field treated the same way...another 30
> Cases.  Is there a limit? and...  Case should be the way to go...right?
> 

It may be better to do this using a series of set based updates, 
particularly if some of the fields you are basing the category on are 
indexed since SQL Server will be able to optimize the record selection.

Update myTable set CategoryA = 1 where...........
Update myTable set CategoryA = 2 where...........
...
Update myTable set CategoryB = 1 where...........
Update myTable set CategoryB = 2 where...........
...

-- 
Stuart