January 19, 2009 at 9:41 am
I'm not that familiar with Functions.
The programmers once had this in the SQL statment & moved it into a Function. I've noticed it creates thousands & thousands of lines in profiler. Should this really be a function? Is there a better way?
This is the calling line in the proc:
UPDATE #events SET county = dbo.GetCountyAbbrev(state, county)
Here's the function:
ALTER FUNCTION [dbo].[GetCountyAbbrev]
(
@state varchar(50),
@county varchar(100)
)
RETURNS char(6)
AS
BEGIN
DECLARE @abbrev char(6)
SET @abbrev = UPPER(@county)-- If a new county is not in the list, this will help us identify it.
SELECT @abbrev =
CASE UPPER(@state)
WHEN 'AZ' THEN-- Arizona
CASE UPPER(@county)
WHEN 'CONCONINO'THEN 'CN'
WHEN 'GRAHAM'THEN 'GR'
WHEN 'LA PAZ'THEN 'LP'
WHEN 'MARICOPA'THEN 'MR'
WHEN 'MOHAVE'THEN 'MH'
WHEN 'PIMA'THEN 'PI'
WHEN 'PINAL'THEN 'PN'
WHEN 'YAVAPAI'THEN 'YV'
WHEN 'YUMA'THEN 'YM'
END
WHEN 'CA' THEN-- California
CASE UPPER(@county)
WHEN 'ALAMEDA'THEN 'AL'
WHEN 'CONTRA COSTA'THEN 'CC'
WHEN 'EL DORADO'THEN 'ED'
WHEN 'FRESNO' THEN 'FR'
WHEN 'IMPERIAL'THEN 'IM'
WHEN 'KERN'THEN 'KN'
WHEN 'KINGS'THEN 'KG'
WHEN 'LOS ANGELES' THEN 'LA'
WHEN 'MADERA'THEN 'MA'
WHEN 'MERCED'THEN 'MC'
WHEN 'ORANGE'THEN 'OR'
WHEN 'PLACER'THEN 'PL'
WHEN 'PLUMAS'THEN 'PU'
WHEN 'RIVERSIDE'THEN 'RV'
WHEN 'SACRAMENTO'THEN 'SC'
WHEN 'SAN BENITO'THEN 'ST'
WHEN 'SAN BERNARDINO'THEN 'SB'
WHEN 'SAN DIEGO'THEN 'SD'
WHEN 'SAN FRANCISCO'THEN 'SF'
WHEN 'SAN JOAQUIN'THEN 'SJ'
WHEN 'SANTA BARBARA'THEN 'SN'
WHEN 'SOLANO'THEN 'SO'
WHEN 'STANISLAUS'THEN 'CASTAN'
WHEN 'TULARE'THEN 'TU'
WHEN 'VENTURA'THEN 'VN'
END
WHEN 'IL' THEN-- Illinois
CASE UPPER(@county)
WHEN 'COOK'THEN 'ILCOOK'
WHEN 'DEKALB'THEN 'ILDEKB'
WHEN 'DUPAGE'THEN 'ILDUPG'
WHEN 'KANE'THEN 'ILKANE'
WHEN 'KENDALL'THEN 'ILKNDL'
WHEN 'LAKE'THEN 'ILLAKE'
WHEN 'MCHENRY'THEN 'ILMCHN'
WHEN 'WILL'THEN 'ILWILL'
END
WHEN 'IN' THEN-- Illinois
CASE UPPER(@county)
WHEN 'JASPER'THEN 'INJSPR'
WHEN 'LAKE'THEN 'INLAKE'
WHEN 'LA PORTE'THEN 'INLAPT'
WHEN 'LAPORTE'THEN 'INLAPT'
WHEN 'PORTER'THEN 'INPRTR'
END
WHEN 'WA' THEN-- Washington
CASE UPPER(@county)
WHEN 'KING'THEN 'WAKING'
WHEN 'PIERCE'THEN 'WAPIER'
WHEN 'SNOHOMISH'THEN 'WASNOH'
WHEN 'SPOKANE'THEN 'WASPOK'
WHEN 'THURSTON'THEN 'WATHUR'
END
END
RETURN @abbrev
END
January 19, 2009 at 9:46 am
How about creating a table for this stuff? a simple lookup table should be infinitely easier to maintain than the function, and will run circles around it perf-wise too...
Static mappings like that belong in tables.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 9:49 am
When you use a function like this then it will be called for each row that needs updating.
It would be far more efficent if you put the values that are in the function into a look-up table and then use a join to update the values
January 19, 2009 at 9:51 am
Thanks for the quick replys.
I'm thinking I should create a new table with state, county, abbrev...
January 19, 2009 at 11:09 am
krypto69 (1/19/2009)
Thanks for the quick replys.I'm thinking I should create a new table with state, county, abbrev...
Yep.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 19, 2009 at 1:05 pm
It would be far more efficent if you put the values that are in the function into a look-up table and then use a join to update the values
Can you give me an example of the join I would use to update?
Thanks allot!
January 19, 2009 at 1:12 pm
update tableA
set countyAbbrev = tb.newCountyAbbrev
from
tableA
inner join tableb tb on
tablea.state=tb.state and
tablea.county=tb.county
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 1:58 pm
Thanks Matt
January 19, 2009 at 7:10 pm
I'll also add that unless your database has been set to be case sensitive, then you don't need the extra overhead of all the UPPER() functions...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2009 at 9:03 am
Thanks Jeff
January 20, 2009 at 9:35 pm
You bet... thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply