Multi language

Question

Multi language

dubem1-878067

Ten Centuries

Points: 1044
More actions
April 23, 2020 at 1:29 am

#3745528
I am dealing with a multi language app. In some tables there is two columns for the name of the entity: FrenchName and EnglishName. When I get the list I need to get either French or English name based on the specified language
I have stored Procedure to get the values, the language id is passed to the stored proc. 1 means French 2 means English.
Here's how I do it, do you think it's the best way or do you have any other way to acheive this?
```
CREATE PROCEDURE GETStatus 
(
    @IdLanguage int
)
AS
BEGIN
SET NOCOUNT ON;

Select IdStatus, CHOOSE(@IdLanguage, NameFr , NameEn) as Name
    from Status order by displayOrder
END
GO
```
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Personally, I'd lean towards using a unicode column and store the data as needed. I mean, what happens when you add German, Swahili and Sanskrit? Translate or format on the client side then.

However, yes, that's not going to cause major performance bottle necks I don't think. I'm assuming of course that you actually have a WHERE clause on the real queries and an index in support of the WHERE clause.

I'd want to do a test to see how CHOOSE worked with INCLUDE to ensure any non-clustered indexes worked well, but, overall, I think you'll be fine. In fact, I think I'll go do that test and write up a blog post.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 2

Thanks for your answer, in fact those values are not entered by end-user. They are values of drodown lists, the end-user select the language at login, then every dropdown must show their values in the language selected. That's why there is two columns in the table. The app support 2 languages.

Would be very curious to read your blog post, share the link when it's there.

thanks

This reply was modified 4 years, 5 months ago by dubem1-878067.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Already part way into testing. With a clustered index, and CHOOSE in the SELECT, no affect on performance whatsoever. Working on the next bit now.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

Good news. You can get nonclustered index use as long as the INCLUDE list contains the columns you're using in the CHOOSE command in the SELECT clause. Blog post will come out on Monday. I'm doing a couple of more tests.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software