1

For a project, we are using a table (named txtTable) that contains all the texts. And each column contains a different language (for example column L9 is English, column L7 is German, etc..). 

TextID L9          L7               L16    L10    L12
------------------------------------------------------
26     Archiving   Archivierung     NULL   NULL   NULL
27     Logging     Protokollierung  NULL   NULL   NULL
28     Comments    Kommentar        NULL   NULL   NULL

This table is located in a database on a Microsoft SQL Server 2005. The big problem is that this database name changes each time the program is restarted. This is a behavior typically for this third-party program and cannot be changed.

Next to this database and on the same server is our own database. In this database are several tables that point to the textID for generating data for reporting (SQL Server Reporting Services) in the correct language. This database contains also a table "ProjectSettings" with some properties like the name of the texttable database, and the stored procedures to generate the reporting data.

The way we now are requesting the right texts of the right language from this table with the changing database name is by creating a dynamic SQL query and execute it in a stored procedure.

Now we were wondering if there is a cleaner way to get the texts in the right language. We were thinking about creating a function with the textID and the language as a parameter, but we cannot find a good way to do this. We thought about a function so we just can use it in the select statement, but this doesn’t work:

CREATE FUNCTION [dbo].[GetTextFromLib]
(
    @TextID  int, 
    @LanguageColumn Varchar(5)
)
RETURNS varchar(255)
AS
BEGIN
    -- return variables
    DECLARE @ResultVar varchar(255)

    -- Local variables
    DECLARE @TextLibraryDatabaseName varchar(1000)
    DECLARE @nvcSqlQuery varchar(1000)  

    -- get the report language database name
    SELECT @TextLibraryDatabaseName = TextLibraryDatabaseName FROM ProjectSettings

    SET @nvcSqlQuery = 'SELECT @ResultVar =' + @LanguageColumn + ' FROM [' + @TextLibraryDatabaseName + '].dbo.TXTTable WHERE TEXTID = ' + cast(@TextID as varchar(30))

     EXEC(@nvcSqlQuery)

    -- Return the result of the function
    RETURN @ResultVar
END

Is there any way to work around this so we don’t have to use the dynamic sql in our stored procedures so it is only ‘contained’ in 1 function?

Thanks in advance & kind regards,

Kurt

Improve this question

2 Answers 2

Reset to default
0

Yes, it is possible with the help of synonym mechanism introduced with SQL Server 2005. So, you can create synonym during your setting up procedure based on data from ProjectSettings table and you can use it in your function. Your code will look something like this:

UPDATE: The code of function is commented here because it still contains dynamic SQL which does not work in function as Kurt said in his comment. New version of function is below this code.

-- Creating synonym for TXTTable table
-- somewhere in code when processing current settings
-- Suppose your synonym name is 'TextLibrary'
--
-- Drop previously created synonym
IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'TextLibrary')
DROP SYNONYM TextLibrary

-- Creating synonym using dynamic SQL

-- Local variables
DECLARE @TextLibraryDatabaseName varchar(1000)
DECLARE @nvcSqlQuery varchar(1000)  

-- get the report language database name
SELECT @TextLibraryDatabaseName = TextLibraryDatabaseName FROM ProjectSettings

SET @nvcSqlQuery = 'CREATE SYNONYM TextLibrary FOR [' + @TextLibraryDatabaseName + '].dbo.TXTTable'

EXEC(@nvcSqlQuery)

-- Synonym created  

/* UPDATE: This code is commented but left for discussion consistency
-- Function code
CREATE FUNCTION [dbo].[GetTextFromLib]
(
    @TextID  int, 
    @LanguageColumn Varchar(5)
)
RETURNS varchar(255)
AS
BEGIN
    -- return variables
    DECLARE @ResultVar varchar(255)

    -- Local variables
    DECLARE @nvcSqlQuery varchar(1000)  

    SET @nvcSqlQuery = 'SELECT @ResultVar =' + @LanguageColumn + ' FROM TextLibrary WHERE TEXTID = ' + cast(@TextID as varchar(30))

     EXEC(@nvcSqlQuery)

    -- Return the result of the function
    RETURN @ResultVar

END
*/

UPDATE This is one more attempt to solve the problem. Now it uses some XML trick:

-- Function code
CREATE FUNCTION [dbo].[GetTextFromLib]
(
    @TextID  int, 
    @LanguageColumn Varchar(5)
)
RETURNS varchar(255)
AS
BEGIN
    -- return variables
    DECLARE @ResultVar varchar(255)

    -- Local variables
    DECLARE @XmlVar XML 

    -- Select required record into XML variable
    -- XML has each table column value in element with corresponding name
    SELECT @XmlVar = ( SELECT * FROM TextLibrary
                       WHERE TEXTID = @TextID
                       FOR XML RAW, ELEMENTS )

    -- Select value of required element from XML
    SELECT @ResultVar = Element.value('(.)[1]', 'varchar(255)') 
    FROM @XmlVar.nodes('/row/*') AS T(Element)
    WHERE Element.value('local-name(.)', 'varchar(50)') = @LanguageColumn

    -- Return the result of the function
    RETURN @ResultVar

END

Hope this helps.

Credits to answerer of this question at Stackoverflow - How to get node name and values from an xml variable in t-sql

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi Serg, The Synonym is a very good idea, but the function itself still doesn’t want to work because you are not allowed to run dynamic sql in functions. Is there a work around to select the column dynamically? When not using the dynamic SQL, the function already works with a fixed column, now it is just a matter of getting the right column as response. Thanks already & kind regards, Kurt
Oh, shame upon me for this fault! I update my answer with trying to avoid dynamic SQL. Sorry...
Hi Serg, This works great! Thanks a lot. Just one more question, what about the performance of this function, is this something that is consuming a lot of resources or can i run this multiple times embedded in a select statement without any problem? Thanks a lot and kind regards, Kurt
Performance questions are most difficult to answer. I could say something more definite if we compare two solutions. But as I can imagine your whole task and its production environment I think that you don't notice influence of this function. I think maximal resources consumes SELECT * FROM TextLibrary ... WHERE ...
0

To me, it sounds like a total PITA... However, how large is this database of "words" you are dealing with. Especially if it is not changing much and remains pretty constant. Why not have on some normal cycle (such as morning), just have one dynamic query generated that queries the one that changes and synchronize it to a "standard" table name in YOUR database that won't change. Then, all your queries run against YOUR version and completely remove the constant dynamic queries every time. Yes there would need to be this synchronizing stored procedure to run, but if it can be run on a schedule, you should be fine, and again, how large is the table of "words" for proper language context.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.