Using Chinese characters in SQL Server stored procedure

I have a stored procedure that is called by a SSIS package. This package calls many stored procedures, but this particular one is the only one causing issues. Basically, I am converting a column in a table from nchar to nvarchar and putting those values into another table.

The values could be English, or they could be Chinese.

  • SQL-How to Insert Row Without Auto incrementing a ID Column?
  • Is there any way to search for numbers with leading zeros in SQL Server Full-text indexes
  • I wanted to know the command to check if all the values in one field of a table is present in another table under a different field name
  • How to get column which has least values among them
  • Are good security practices and auto identity range mgmt mutually exclusive?
  • delete row from selected gridview and database
  • Here is the code:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[TransformCustomer]
    AS
        DECLARE @MapDimCustomer TABLE
                                (
                                     SourceSystemId INT,
                                     ExCustomerKey NVARCHAR(15),
                                     CustomerName NVARCHAR(115),
                                     CustomerTypeName NVARCHAR(50)
                                )
    
        INSERT INTO @MapDimCustomer (SourceSystemId, ExCustomerKey, CustomerName, CustomerTypeName)
            SELECT
                4 As SourceSystemId,
                c.ABAN8 As ExCustomerKey,
                c.ABALPH As CustomerName,
                c.ABAT1 As CustomerTypeName
            FROM
                F0101 c
            LEFT JOIN
                GlobalDW.stage.DimCustomer gc ON c.ABAN8 = gc.ExCustomerKey 
                                              AND gc.SourceSystemID = 4
             WHERE
                 gc.CustomerID IS NULL;
    
        SELECT * FROM @MapDimCustomer;
    

    So, where it says

    DECLARE @MapDimCustomer TABLE
    (
      SourceSystemId int,
      ExCustomerKey nvarchar(15),
      CustomerName nvarchar(115),
      CustomerTypeName nvarchar(50)
    )
    
    INSERT INTO @MapDimCustomer(SourceSystemId, ExCustomerKey, CustomerName, CustomerTypeName)
    

    CustomerName is where it can be English or Chinese. When I run this, it turns the Chinese symbols into ?????. I know that, if I were entering a customer name, I would have to use N'Chinese Characters here'. Is there a way to declare that?

    One Solution collect form web for “Using Chinese characters in SQL Server stored procedure”

    As an answer to your question about looking at the first character to determine the UNICODE value it would be something like this.

    SELECT 4 As SourceSystemId
        , c.ABAN8 As ExCustomerKey
        , case when UNICODE(left(c.ABALPH, 1)) > 127 then c.ABALPH else c.ABDC end As CustomerName
        , c.ABAT1 As CustomerTypeName
    FROM F0101 c
    LEFT JOIN GlobalDW.stage.DimCustomer gc ON c.ABAN8 = gc.ExCustomerKey 
                                        AND gc.SourceSystemID = 4
    WHERE gc.CustomerID IS NULL;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.