Geek Answers Handbook

Column sorting for different mappings in SQL Server

I have a company table with a multilingual column CompanyName (defined as nvarchar (512)).

I also have a stored procedure for searching and returning a list of companies, it takes 2 nvarchar parameters - one of them is a search query, and the other is an ISO language code.

What I would like to do is to return search results sorted by sorting corresponding to the language code specified in the second parameter, for example:

SELECT * FROM dbo.Companies WHERE CompanyName LIKE '%searchstring%'
ORDER BY
    CASE @lang
        WHEN 'sv' THEN CompanyName COLLATE Sami_Sweden_Finland_100_CI_AI
        WHEN 'ch' THEN CompanyName COLLATE Chinese_Simplified_Pinyin_100_CI_AI
                ELSE CompanyName
    END

however, I get the following error when I try to run it:

Cannot resolve collision conflict between "Chinese_Simplified_Pinyin_100_CI_AI" and "Sami_Sweden_Finland_100_CI_AI" in a CASE operation.

- , , ? .

sql, , , , 20 ( 1), 2 .

, , , , - , (.. )?

+5
2

Acolumn . CASE , . :

SELECT * FROM dbo.Companies WHERE CompanyName LIKE '%searchstring%'
ORDER BY
    CASE @lang
        WHEN 'sv' THEN CompanyName ELSE '' END 
    END COLLATE Sami_Sweden_Finland_100_CI_AI,
    CASE @lang
        WHEN 'ch' THEN CompanyName ELSE '' END 
    END Chinese_Simplified_Pinyin_100_CI_AI
+2

, , , , @gbn, , , , .

, , Dynamic SQL . , ( )

CompanyName COLLATE [Collation_Name]

, ( , , , , ).

EXEC('SELECT TOP 10 * FROM dbo.Companies
      WHERE CompanyName LIKE ''%searchstring%''
      ORDER BY CompanyName COLLATE ' + @collation)

, . , , @gbn, , , , .

, , Dynamic SQL - , , (.. , )

+2

More articles:


All Articles