Last post Aug 02, 2007 07:25 PM by NeoRev
Jul 23, 2007 04:48 PM|NeoRev|LINK
We have an existing C# application that uses SQL Server 2000 database. Currently, the app is English-only, however the business now wants to internationalize the app to support multiple languages (Spanish, Russian, Chinese, Korea, etc.). We will have only
one database. So, data in multiple languages will be stored in the same database.
My question is about how this affects the ORDER BY clauses in our SQL stored procedures.
I found an article on Microsoft's web site which seemed to imply that we will have to use COLLATE on every single ORDER BY. If this is true, that means we'll have to pass down a collation name to the stored procedures and then have huge CASE blocks for
This seems crazy to me.
Can anyone clear this up?
Jul 23, 2007 09:46 PM|Javier Luna|LINK
You would to have sort your data using Comparer<T> a level collections in Business Layer.
Jul 31, 2007 01:17 PM|NeoRev|LINK
Thanks, I'm new to internationalization and I'm curious how everyone else is solving this problem. So far, I've only got one response. Nobody else needs to sort data from SQL Server?
Aug 01, 2007 09:17 PM|rstrahl|LINK
Not to sound snotty, but how else would you specify sort order? There's no way for SQL Server to know what your intent is unless you tell it. Somewhere - whether its in the database or in the business layer - you would have to specify how the data needs
to be sorted and I'd guess doing it at the data level (via business layer logic hopefully) would be the most efficient.
Aug 02, 2007 07:25 PM|NeoRev|LINK
Well, I can think of 5 possible solutions for this:Quote
1) Pass the culture down to the stored procedure and have huge CASE statements for the different ORDER BY clauses.
2) Create separate stored procedures, one for each culture.
3) Design the database so that each culture has its own column for strings. Then set column-level collation sequence.
4) Design the database so that each culture has its own table for strings. Then set table-level collation sequence.
5) Do the sorting in the application.
To be honest, I don't like any of them.