a
?
b
c
d
......
but when we sort this data by
"select column1 from table where column2='true' order by column1 collate Danish_Norwegian_CS_AS "
the result set will be like
......
v
w
x
y
z
?
so we want the second one in other words the alphabethic sort order,
we can get the same result set by .net side by defining cultureinfo to danish and bind the data into a dataset and sort the data in a dataview. I want to learn that which way is best on sql side or on .net side.
If your answer will be sql side how can i recieve the client collate to my stored procedur.
i wrote a procedur like this
ALTER procedure dbo.dt_getproductcollation_test
@.collation nvarchar(50)
AS
DECLARE @.cmd nvarchar(3000)
set nocount on
SET @.cmd='select column1 from tcollate where [column2]=1 ORDER BY COLUMN1 COLLATE ' + @.collation
Execute(@.cmd)
but by this way i gues my procedure wont be compile because of execute statement, and also this will reduce my procedure performans.
is anybody has a best practises about this issue.
Thank You.
If it is easy to implement and the result set is not too big, I think it would be better to sort it on middle tier/client, i.e., on .NET side. This will avoid the complicated logic to do dynamically COLLATE on SQL side, and save some server cycles.
As you said, using dynamic SQL in the stored procedure will cause recompile every time, which is certainly not good. You could build multiple version of the same proc to handle different collations, or build the SQL statements on the client, but neither is a good/convenicent option.
No comments:
Post a Comment