Why does my INSERT INTO does not work well with NVARCHAR?

Here is my code with a stored procedure. The result table when SELECTing does not show the NVARCHAR() type as intended but instead seems like VARCHAR().

CREATE TABLE [User]   (      [UserId] int IDENTITY(1,1),      [FirstName] nvarchar(50) NOT NULL,      [LastName] nvarchar(50) NOT NULL,      [Sex] varchar(6) CHECK([Sex] IN('Male', 'Female')) NOT NULL,      [IdentificationNumber] varchar(20) UNIQUE NOT NULL,      [DOB] date NOT NULL,      [Address] nvarchar(100) NOT NULL,      [Email] nvarchar(100) NOT NULL,      [Role] varchar(13) CHECK([Role] IN('Teacher', 'Student', 'Administrator')) NOT NULL,      [PasswordHash] Binary(64) NOT NULL,      [Salt] UNIQUEIDENTIFIER,      PRIMARY KEY ([UserId])  );  

CREATE PROCEDURE [AddUser]      (@pFirstName nvarchar(50),       @pLastName nvarchar(50),       @pSex varchar(6),       @pIdentificationNumber varchar(20),       @pDOB date,       @pAddress nvarchar(100),       @pEmail nvarchar(100),       @pRole varchar(13),       @pPassword nvarchar(50),       @ResponseMessage nvarchar(250) OUTPUT)  AS  BEGIN      IF @pSex NOT IN ('Male', 'Female')      BEGIN          RAISERROR ('Sex should be either Male or Female', 0, 1)           RETURN      END            IF @pRole NOT IN ('Teacher', 'Student', 'Administrator')      BEGIN          RAISERROR ('Role should be Teacher, Student, or Administrator', 0, 1)          RETURN      END            SET NOCOUNT ON      DECLARE @salt UNIQUEIDENTIFIER=NEWID()            BEGIN TRY          INSERT INTO [BeszeDB].[dbo].[User] (FirstName, LastName, Sex, IdentificationNumber, DOB, Address, Email, Role, PasswordHash, Salt)          VALUES (@pFirstName, @pLastName, @pSex, @pIdentificationNumber, @pDOB, @pAddress, @pEmail, @pRole, HASHBYTES('SHA2_512', @pPassword + CAST(@salt AS NVARCHAR(36))), @salt)            SET @ResponseMessage = 'Success'      END TRY      BEGIN CATCH          SET @ResponseMessage = ERROR_MESSAGE()      END CATCH  END  

DECLARE @ResponseMessage nvarchar(250)    EXEC [AddUser]         @pFirstName =  'Trần Triệu',         @pLastName= 'Trịnh',         @pSex = 'Male',         @pIdentificationNumber = '12345',         @pDOB = '2000-09-29',         @pAddress = '235 Đường Giải Phóng',         @pEmail = 'minhhoangtrinh231@gmail.com',         @pRole = 'Student',         @pPassword = '123456',         @ResponseMessage = @ResponseMessage OUTPUT    SELECT @ResponseMessage AS N'@ResponseMessage'  

As a result, when I SELECT from the [User] table, the special Unicode letter (like 'ầ','ệ','ị',...) is expressed as "?"

I think that the problem lies in the INSERT statement as the table can still store NVARCHAR correctly when I edit it manually.

The Result will look like this

https://stackoverflow.com/questions/66666733/why-does-my-insert-into-does-not-work-well-with-nvarchar March 17, 2021 at 11:50AM