[dba-SQLServer] SPAM-LOW: Re: Is there a better way

Doris,

Thanks for the response but the tests have to be over several different fields.  I will use If Else.

this is the first UDF I have written and of course I am up against the learning curve.  ATM I need 
to figure out how to get a test query running.  When I try to add the UDF as a field of the query it 
gives me an error "not a recognized function".  I assume that perhaps I have to have some prefix in 
my UDF similar to the sp_ required for stored procedures?  I haven't found it yet on Google.

John W. Colby
www.ColbyConsulting.com


Doris Manning wrote:
> Take a look at CASE WHEN in the BOL.
> 
> Doris Manning
> Database Administrator
> Hargrove Inc. 
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Saturday, July 04, 2009 8:41 AM
> To: Dba-Sqlserver
> Subject: [dba-SQLServer] Is there a better way
> 
> I am updating a flag field which tells me that an address is valid.  The
> code currently consists of a series of about FIVE passes through the table
> doing things like:
> 
> Update MyTbl
> SET AddrValid = 'ANK'
> WHERE ANK_ IS NOT NULL
> 
> Update MyTbl
> SET AddrValid = 'PO'
> WHERE (AddrValid IS NULL) AND (Addr Like 'PO%')
> 
> 
> Update MyTbl
> SET AddrValid = 'MOV'
> WHERE (AddrValid IS NULL) AND (Addr Like 'MOVED%')
> 
> Etc next code
> 
> Etc Next code
> 
> My objective is to have a moved flag that I can find all of my ANK records,
> or EXCLUDE the ANK and Moved etc.
> 
> Obviously I have a ton of processing going on here.  It has to do N passes
> through the table to find and create N different codes.  With tens of
> millions of records this is taking a large while.
> 
> Is there a faster way?  Drop the where and put the where processing into a
> UDF that figures out what the code should be.  So every record gets
> processed, and the UDF takes the fields in the where clause and decides what
> code to assign.
> 
> Something like that.  Would it be faster?  Given that we are by design
> visiting every row I would think it would be, though it might be a complex
> function to design.
> 
> 
>