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

Actually, the best solution would be to generate 2 lookup tables and link them
to the first one based on the key in the fields you are trying to do the case
for.  It will be much faster, and easier to maintain later.

Robert L. Stewart
The Dyson Group International
Software for the Non-profit Enterprise
Expanding your Sphere of Knowledge


Quoting dba-sqlserver-request at databaseadvisors.com:

> Date: Wed, 21 Jun 2006 03:19:22 +1000
> From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
> Subject: Re: [dba-SQLServer] If or Case or 'what'?
> To: dba-sqlserver at databaseadvisors.com
> Message-ID: <4498BABA.5809.250CFE24 at stuart.lexacorp.com.pg>
> Content-Type: text/plain; charset=US-ASCII
>
> 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