Update non-English data in stored procedure using a variable?

Question

I have a stored procedure which accepts a variable which contains data in non-English language. I know that we can use N to do so. But it does not work with variables. How can I go about to achieve that?

I want to do something like this, but it does not work. Below is my stored procedure.

Create Procedure tempUpdateCustomerSurvey
(
    @FeedbackText nvarchar(1000)
)
As
Begin
    Update  tblCustomerSurvey
    Set     GeneralFeedbackText = @FeedbackText
    Where   CustomerSurveyID = 1000 
End

It stores '??????' in the table.

If I execute this SP with data like below, it does not work.

Declare @GeneralFeedbackText Nvarchar(1000) = 'नमस्कार'
Exec tempUpdateCustomerSurvey @GeneralFeedbackText

Know that the stored procedure accepts data from C# code. Below is the core C# method that calls the stored procedure.

public static Feedback InsertSurvey(int CustomerSurveyID, string GeneralFeedbackText)
   {
        try
        {
            int intDBReturnValue = Convert.ToInt32(SqlHelper.ExecuteScalar(SystemSettings.GetDBConnection(),
                                                                "tempUpdateCustomerSurvey",
                                                                CustomerSurveyID,
                                                                GeneralFeedbackText));

            Feedback f = new Feedback(FeedbackService.DB_OP_SPECIFICATION.INSERT, intDBReturnValue, "Customer survey");
            f.ProcessExceptionOrCustomErrorIfAvailable(-1, "System couldn't verify the survey information.");
            return f;
           }
           catch (Exception ex)
           {
                return new Feedback(FeedbackService.DB_OP_SPECIFICATION.INSERT, ex, "Customer survey");
           }
      }

The problem is the way you **initialize** your variable values - in your first example, you're **NOT** using the `N` prefix for the string literal, so this is converted back to a non-Unicode varchar string - that's why you get those question marks in your table.... **works as designed** - just **always** use the `N` prefix when initializing `nvarchar` variables from string literals! — marc_s, Feb 10 '20 at 08:52
If you already know the second code snippet works what are you asking here "How can I go about to achieve that?" - use the snippet that works — Martin Smith, Feb 10 '20 at 08:57
Possible duplicate of [What does N' stands for in a SQL script ? (the one used before characters in insert script)](https://stackoverflow.com/q/14353238/2029983) or [What is the purpose of putting an 'N' in front of function parameters in TSQL?](https://stackoverflow.com/q/2448203/2029983), or perhaps [Why is sql server storing question mark characters instead of Japanese characters in NVarchar fields?](https://stackoverflow.com/q/761036/2029983) — Thom A, Feb 10 '20 at 09:03
you need to show the calling code. It you are passing a string literal it should look like `EXEC tempUpdateCustomerSurvey N'Yorvalue'` - not `EXEC tempUpdateCustomerSurvey 'Yorvalue'` — Martin Smith, Feb 10 '20 at 09:06
If this is storing `'??????'` then you passed `'नमस्कार'` to your parameter, not `N'नमस्कार'`. That much we can infer safely. You need to fix how you call the SP. — Thom A, Feb 10 '20 at 09:06
@Larnu How can I fix how I call the SP? How should I call the SP? — Komal R, Feb 10 '20 at 09:07
You would do `EXEC dbo.tempUpdateCustomerSurvey @FeedbackText = N'नमस्कार';` @KomalR . (Assuming the default schema is `dbo`, as the SP has been created without the schema declared.) — Thom A, Feb 10 '20 at 09:08
How are you calling it at the moment? Straight ADO.NET? If so just ensure `@FeedbackText` is using a parameter and has correct datatype — Martin Smith, Feb 10 '20 at 09:10
@KomalR you just deleted the most important part of the question. It's meaningless now and should be closed as `can't reproduce`. Post how the line that passes the non-English text. That's where the problem is — Panagiotis Kanavos, Feb 10 '20 at 09:11
@KomalR btw *all* SQL Server libraries work perfectly with Unicode - provided you do pass the text as Unicode instead of ASCII. If you use string concatenation (bad idea in itself) and forget the `N`, you're using ASCII. If you use parameterized queries with `nvarchar` parameters though, you'll have no problems — Panagiotis Kanavos, Feb 10 '20 at 09:13
@KomalR .NET strings are Unicode. Windows strings are Unicode. ADO.NET works perfectly with Unicode, and your own question proves it. The problem is a bug in the C# code. Post the client code — Panagiotis Kanavos, Feb 10 '20 at 09:15
`@GeneralFeedbackText Nvarchar(1000) = 'नमस्कार'` you forgot the `N` prefix here so the string is treated as ASCII. If you use parameterized queries in C# though, you won't have to worry about this at all. — Panagiotis Kanavos, Feb 10 '20 at 09:17
What is `SqlHelper`? Are you using the ancient Microsoft Application Blocks? If so it looks like it *ought to* get the correct parameter datatype from the code here http://www.agilechai.com/source/SqlHelper-Source-Code-cs.html. You could try passing in parameters instead of the raw values to ensure the correct type is used (and call overload `ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)` — Martin Smith, Feb 10 '20 at 09:40
Though actually you should be calling `ExecuteNonQuery` any way as the stored proc doesn't return a scalar result — Martin Smith, Feb 10 '20 at 09:43

score 1 · Answer 1 · answered Feb 10 '20 at 08:54

1

The problem is here

Declare @FeedbackText nvarchar(1000) = 'नमस्कार'

You should use N prefix like this

Declare @FeedbackText nvarchar(1000) = N'नमस्कार'

See the following image to know the difference from between them

answered Feb 10 '20 at 08:54

Nguyễn Văn Phong

13,506
17
39
56

Please read my question properly. I know that prefixing N to the string works, but I have a dynamic SP, which accepts data from C# code. – Komal R Feb 10 '20 at 08:59
So you just declare the type of `@FeedbackText` param is Nvarchar(1000) – Nguyễn Văn Phong Feb 10 '20 at 09:00
It does not work. – Komal R Feb 10 '20 at 09:01
1

@KomalR - your question doesn't mention anything about "dynamic SP" (what ever that is) and it isn't clear what your issue is. If you are creating a string literal append it with `N`. If you are using a parameter ensure it is `nvarchar`. If you have code that doesn't work add that to your question so we can see what you are doing wrong. – Martin Smith Feb 10 '20 at 09:02
Yes, I understood that, I didn't clarify. I have updated my question. – Komal R Feb 10 '20 at 09:05
@KomalR it works and you yourself proved it does - SO is a .NET site storing data in SQL Server, in `nvarchar` fields. You wouldn't be able to post `नमस्कार` otherwise. If you have issues, it's your C# code – Panagiotis Kanavos Feb 10 '20 at 09:14
Okay, can you tell me what could be the issue with my C# code? the data type of the variable in C# code is string. – Komal R Feb 10 '20 at 09:15
1

@KomalR *post your code*. The variable is a string but how do you pass that parameter? Did you use string concatenation? That's a bug. Did you use parameterized queries? What type did you specify for the parameter? `NVarchar` or `Varchar`? If you used string concatenation, did you use the `N` prefix? – Panagiotis Kanavos Feb 10 '20 at 09:17
@KomalR and I'll repeat it - *your own question* proved that Unicode just works in .NET. You wouldn't be able to post `नमस्कार` otherwise, and I wouldn't be able to post `Αυτό Εδώ` without contacting SO.The two codepages couldn't be farther apart, and yet, both appear just fine in the same string – Panagiotis Kanavos Feb 10 '20 at 09:19
From my point of view, you should catch profile to check the value sent from C# to SQL to be able to know what's exactly the value `@FeedbackText`. – Nguyễn Văn Phong Feb 10 '20 at 09:23
From HTML to code behind, I do get the value as it is, but it does not store it in the DB as it is. – Komal R Feb 10 '20 at 09:24

Update non-English data in stored procedure using a variable?

1 Answers1