SQL Server 2005 or 2008 required for this.
I don’t pretend to be a fanboy of dynamic SQL but there are times when it is needed. I came across such a situation today where I needed to update an aging procedure which had lumps of hard coded T-SQL.
Consider this scenario. You have full text catalogs for each culture your system supports; in our environment this is only Dutch, British English and German (1043, 2057 and 1031). These have to be separate tables due to the nature of full text. However, for one of our processes we need to union join the results.
Now, in the original procedure this was achieved by hard-coding the table names. When a new culture is added to our system , we would have to change the procedure again. If this was the only procedure that needed changing I would almost consider doing this but we have 200+ procedures that have an element to them where the culture is used.
So, it was decided that we needed to change this and others so that the addition of a new culture is not an issue. Right, back to the original procedure. We already have a Culture table; this holds the CultureId, the Culture (as in en-GB, nl-NL) and the descriptive Culture (e.g. British English). For the purpose of dynamically building my query I need to produce a query that gives me a string that holds the Union T-SQL.
So you can see this in action, here is the T-SQL to create the Culture table and fill it with some data:
CREATE TABLE Culture (CultureId INT, Culture CHAR (5) ,
CultureLanguage VARCHAR (25))
INSERT INTO Culture (CultureId, Culture, CultureLanguage)
VALUES (1043, ‘nl-NL’,”)
INSERT INTO Culture (CultureId, Culture, CultureLanguage)
VALUES (2057, ‘en-GB’,”)
INSERT INTO Culture (CultureId, Culture, CultureLanguage)
VALUES (1031, ‘de-DE’,”)
Now you have a table with some culture values in, this is my solution to generating the Union T-SQL:
DECLARE @UnionSQL NVARCHAR (500)
SET @UnionSQL = (
SELECT DISTINCT
STUFF((
SELECT ‘UNION ALL SELECT MyId FROM [Visits_' +
T2.Culture + '] WITH (NOLOCK) ‘
FROM Culture T2
FOR
XML PATH(”)),1,9,”) AS Cultures
FROM Culture T1
)
PRINT @UnionSQL
In our environment , all the culture based full text tables are named with the Culture extension so the select is really simple. The clever bit is the use of FOR XML PATH and STUFF. This produces the following output:
SELECT MyId
FROM [Visits_nl-NL] WITH (NOLOCK)
UNION ALL
SELECT MyId
FROM [Visits_en-GB] WITH (NOLOCK)
UNION ALL
SELECT MyId
FROM [Visits_de-DE] WITH (NOLOCK)
For me, this is a really simple solution and it means that any new cultures added to the Culture table are automatically included in the procedure. I do have some additional checks prior to this to ensure the tables exist plus obviously a lot more code within the procedure itself.
This particular procedure runs once a day for a process we have , I am sure this may not be the best way to achieve the solution if you needed to run this 100 times a minute and I would welcome any comments on this scenario and alternative solutions. For instance, I know you can achieve the same result with CTEs.
