Search code examples
sql-servert-sqlcollate

Specify sort collation for a SELECT query extending the basic collate statement for a multilingual app

I have a requirement for a multilingual application, where the columns displayed on a search are picked up from configuration, and the data can be sorted based on configuration.

Question 1 - I am using a Select query like this:

SELECT * 
FROM TABLE 
ORDER BY 1 COLLATE SQL_Latin1_General_CP1_CI_AS DESC,
ORDER BY 4 COLLATE SQL_Latin1_General_CP1_CI_AS DESC

I am getting the following error:

Expression type int is invalid for COLLATE clause.

Is it possible to make this work without specifying the column name and by only using the column index the way we write normal SQL queries?

Question 2 - I would also need to support integer column based sorting like "ID"

But I'm getting the same error as mentioned for question 1. Is there any workaround to allow this? Or is this a SQL Server limitation that we have to live with even with the latest version of SQL Server 2016?

Would appreciate any response/help.

Solution

  • It is not best practise to use column index values in SQL queries Even it does not work in many syntax

    So it is best to use column names in your queries

    For COLLATE statement, you can use it for character data type field values. For numeric data types, you can not use COLLATE.