This question already has an answer here:
COLLATE in UDF does not work as expected
I have the following function for setting COLLATE for the column @p based on the @c parameter. I was inspired by this answer.
CREATE FUNCTION [dbo].[fnsConvert]
IF ( @c = 'sv-SE' )
SET @p = @p COLLATE Finnish_Swedish_100_CI_AS
SET @p = @p COLLATE SQL_Latin1_General_CP1_CI_AS
This is how I use it:
SELECT Title FROM Things
ORDER BY dbo.fnsConvert(Title, 'sv-SE')
In an attempt to debug it I run
DECLARE @l nvarchar(255)
DECLARE @P nvarchar(255)
EXEC @l = dbo.fnsConvert
@p = Test, @c = 'sv-SE';
This just return the string Test. I would expect it to also contain the COLLATE statement but I could very well be wrong about that.