Problem Statement:
We are having a legacy application with backend as SQL Server. Till now, we did not face any issues in passing non-unicode values. Now, we are getting unicode characters from user interface.
The unicode character is getting passed as given below, in UI. These data are being inserted into table.
Currently, we pass unicode characters like below and we are losing non-english characters.
EXEC dbo.ProcedureName @IN_DELIM_VALS = '삼성~AX~Aland Islands~ALLTest1~Aland Islands~~~~'
What we tried:
If we pass unicode with N prefix, the non-english characters are being inserted into table properly.
EXEC dbo.ProcedureName @IN_DELIM_VALS = N'삼성~AX~Aland Islands~ALLTest1~Aland Islands~~~~'
But, adding N prefix, requires UI code change. As it is legacy application, we want to avoid UI change. We want to handle in the sql server side.
when I read about passing parameter without N prefix, the data is implicitly converted to default code page and korean characters are getting lost. Reference
Prefix a Unicode character string constants with the letter N to signal UCS-2 or UTF-16 input, depending on whether an SC collation is used or not. Without the N prefix, the string is converted to the default code page of the database that may not recognize certain characters. Starting with SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, the default code page is capable of storing UNICODE UTF-8 character set.
Our Ask:
Is there a way to add N prefix to the stored procedure parameter, before being assigned to stored procedure parameter and so, we are not losing unicode characters.
Advertisement
Answer
As it is not possible to add N prefix, after parameter is being passed to SQL Server, we are going with below application code change. Ideally, the application should pass the parameter with right nvarchar datatype, so that it is having N prefix.
SqlCommand cmd = new SqlCommand("EXEC dbo.ProcedureName @IN_DELIM_VALS", myConnection);
cmd.Parameters.Add(new SqlParameter("@IN_DELIM_VALS", SqlDbType.NVarChar,400)).Value
= "삼성~AX~Aland Islands~ALLTest1~Aland Islands~~~~";
cmd.ExecuteNonQuery();