[thelist] [MS SQL Trickery] Is my technique good, bad, or neither?

Joshua Olson joshua at waetech.com
Thu Aug 21 09:17:08 CDT 2003


Hi Listers...

I need an opinion here.  I just found a solution to a problem using what I
consider trickery and I want to see if anyone knows WHY I shouldn't do it.

I have a multi-language application in development.  There are two tables
for each item.  One table holds the non-presentational information (pk,
date, data, etc) and another table that holds the presentational information
for each item for every available language.  This is a typical one-to-many
setup.

I wanted to be able to assign a default language to the system as a
fail-over just in case an item didn't have text for a particular language.
I decided I wanted to create a view that would contain only the items table
joined with the default language's records in the supporting table.

I also decided that they should be able to change the default language
whenever they wanted to.  So, instead of programmatically rewriting the view
every time they wanted to change the default language, I instead created a
UDF and based the view definition on it... example:

CREATE FUNCTION dbo.getDefaultLanguage ()
    RETURNS int AS
    BEGIN
      RETURN 11
    END

CREATE VIEW dbo.view_items_default_language
AS
SELECT
    item.field1
  , item.field2
  , ...
  , item.fieldX
  , item_language.name
  , item_language.description
FROM         dbo.item
INNER JOIN dbo.item_language
ON dbo.item_language.language_id = dbo.getDefaultLanguage()
AND dbo.item_language.item_id = dbo.item.id


I checked the validity of my solution by programmatically updating the UDF
and rerunning the view and it *seemed* to work without hiccups.  Any
thoughts as to whether this is a good idea or not?

TIA,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168



More information about the thelist mailing list