December 15, 2008 at 1:34 pm
I use a case statement in quite a few of my queries to return a school name from a list of site id's. I am trying to figure out how to creat a function that would pass the site id from the table into the function and return the school name. Any help appreciated.
CASE SiteID
WHEN 'AL-FDS' THEN'Abraham Lincoln'
WHEN 'AC-FDS' THEN'Acacia'...
...WHEN 'WA-FDS' THEN'Washington'
ELSE'No Site'
END'School'
Bob W
December 15, 2008 at 1:37 pm
A better way would be to join to a table of School Names keyed by SiteID.
[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]
December 15, 2008 at 2:48 pm
That is how I am handling the information now. The issue is this is the same information that I have in 11 queries. I am trying to find a way of getting that information without joining the tables and doing a select statement each time.
Bob
December 15, 2008 at 3:42 pm
But Joining is the best way to get it.
Besides, is: dbo.fnSchoolName(SiteID)
really that much easier than this?: [Left] Join Schools on Schools.SiteID = tblBase.SiteID
Sure, it's a little bit easier, but it is also usually a whole heck of a lot slower too. Plus there will be many circumstances where the JOIN will actually work out to be easier because you will be able to add/use other columns from the Schools table at the same time.
[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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply