|
|
Posted by Beowulf on 06/21/06 15:24
The problem:
I'm updating a report to be "multi-language" capable. Previously,
any items that had text associated with them were unconditionally
pulling in the English text. The database has always been capable of
storing multiple languages for an item, however.
Desired output:
Given the test data below, I'd like to get the following results
select * from mytestfunc(1)
Item_Id, Condition, QuestionText
1876, NOfKids <= 10, This many children is unlikely.
select * from mytestfunc(2)
CheckID, Condition, QuestionText
1876, NOfKids <= 10, NULL
The current SQL for my UDF:
CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (
SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
tblQuestionText.QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
tblQuestionText.Item_ID)
WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR
(tblQuestionText.LanguageReference IS NULL))
)
Test data:
CREATE TABLE [dbo].[tblCheck] (
[Item_ID] [int] NOT NULL ,
[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCheck] ADD
CONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED
(
[Item_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLanguage] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [int] NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Spoken] [bit] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADD
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLanguage] ADD
CONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR
[CreationDate],
UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOCREATE TABLE [dbo].[tblQuestionText] (
[Item_ID] [int] NOT NULL ,
[LanguageReference] [int] NOT NULL ,
[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SameAs] [int] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblQuestionText] ADD
CONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED
(
[Item_ID],
[LanguageReference]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
INSERT INTO tblCheck (Item_ID, CheckDescr)
VALUES(1876, 'NOfKids <= 10')
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(1,1,'English', 1)
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(2,2,'Espanol', 1)
INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)
VALUES (1876, 1, 'This many children is unlikely.')
Any tips or pointers will be appreciated. Thanks.
Navigation:
[Reply to this message]
|