SQL Server : insert Arabic letter to database

Question

First of all, this problem doesn't exist when the text is just English, but when I insert Arabic text, I got the problem.

Look at my code

CREATE PROCEDURE insertToPinTableCardActivation
   (@callerID VARCHAR (200),  
    @vAccount VARCHAR (200))
AS
BEGIN
    DECLARE @textEnglish NVARCHAR(1000) --missing length previously

    SET @textEnglish = 'Dear customer, your Card linked to account number '+ @vAccount --missing set keyword
    SET @textEnglish = @textEnglish + ' is now activated. Thank you for banking with us.'
    SET @textEnglish = 'عزيزي الزبون، تم تفعيل بطاقة الصراف الآلي التابعة لحسابكم رقم ' + @vAccount

    INSERT INTO pinData([CallerID], [body], [processed]) 
    VALUES (@callerID, @textEnglish, 0)
END

The code creates a string of mix Arab and English, and then insert it to a table.

My problem is that look what it is being inserted to the table

even though i already made the field body as nvarchar

could you help please

Update 1

I am inserting from my sql server when executing the stored procedure

update 2

if i go to the table and insert the data manually in arab, the arab letters shows correctly

Answer 1

IF you want to insert Unicode string literals, you must prepend your string literal with an N prefix - try this:

SET @textEnglish = N'...(insert your Arabic text here)...'

Otherwise, your text is reverted back to a non-Unicode format before being stored - and that's why you lose the Arabic text....

And also: if you're concatenating with VARCHAR parameters, I'd recommend using an explicit CAST to NVARCHAR (include a length when casting!):

SET @textEnglish = N'Dear customer, your Card linked to account number ' + CAST(@vAccount AS NVARCHAR(100))