If I understand your requirements correctly....
I would just use the natural key, LanguageCode-CultureCode ("en-US," for example). It's small enough. (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)
CREATE TABLE [dbo].[Language](
[Language] [char](2) NOT NULL,
[Culture] [char](2) NOT NULL,
[LanguageCode] AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[Language] ASC,
[Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED
(
[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Language_Text](
[LanguageID] [varchar](5) NOT NULL,
[LanguageCode] [varchar](5) NOT NULL,
[LanguageName] [nvarchar](20) NULL,
CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED
(
[LanguageID] ASC,
[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Language_Text] WITH CHECK ADD CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO
ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO
This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE. Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.
Edit--I'm sorry, you're right, no search by Culture in my old code. Revamped above, sorry. Here's a sample of content:
Language:
Language-Culture-LanguageCode
en US en-US
sv SE sv-SE
Language_Text:
LanguageID-LanguageCode-LanguageName
en-US en-US English
en-US sv-SE Engelska
sv-SE en-US Swedish
sv-SE sv-SE Svenska
Searching by Culture (Canada):
SELECT test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM test.dbo.Language_Text INNER JOIN
test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE (test.dbo.Language.Culture = 'CA')
Searching by Language (French):
SELECT test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM test.dbo.Language_Text INNER JOIN
test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE (test.dbo.Language.Language = 'fr')
Searching by LanguageCode (Swedish):
SELECT LanguageName
FROM [test].[dbo].[Language_Text]
where (LanguageID = 'sv-SE')
You could try subqueries
However, a subquery that returns more than one value will fail
This works but you have to consider that it can only return one value, and it could break easily
DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)
INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(3,4,5,1)
DECLARE @Parameter int = 0
SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter = 0
UNION ALL
SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 0 ) as col3
FROM @table
WHERE @Parameter = 1
Result:
col1 col2 col3
1 2 5
3 4 5
5 will be returned for each col1 and col2 value, because parameter = 0.
This fails
DECLARE @table TABLE(col1 int,col2 int, col3 int, some_condition int)
INSERT INTO @table(col1,col2,col3,some_condition)
values(1,2,3,0),(2,3,4,0),(3,4,5,1),(6,7,8,1)
DECLARE @Parameter int = 0
SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 1 ) as col3
FROM @table
WHERE @Parameter = 0
UNION ALL
SELECT col1,col2, (select col3 FROM @table WHERE some_condition = 0 ) as col3
FROM @table
WHERE @Parameter = 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
Best Answer
As billinkc noted in his comment there is no way to propagate the subreport errors to the main reports.
As evidenced in this MSDN post you could try to use data driven subscriptions to drive the subscription, but that could get messy if there is any complexity involved real quick.
If you look at the SQL Agent jobs for a server using SSRS subscriptions you will see a bunch of SQL Agent jobs with a GUID as name, you could modify those jobs to include an extra step running some data validation steps before actually running the report failing the job before the report is sent out (risking your job definition being overwritten by SSRS) or calling the job from another job after validating the data logic.
You could identify the GUID associated with the report using this query:
(taken from here)
TL/DR: there is no clean solution but you could get away with some hacks, you would need to implement logic in a data driven subscription or create an extra job/modify the generated job
PS: I didn't test any of these approaches