MS SQL Server CAST to int returns unexpected output

Question

OS type: Windows SQL Server: 2017 Collation: SQL_Latin1_General_CP1_CI_AS

I have a stored procedure as below

USE [test]
GO

ALTER PROCEDURE [dbo].[output_test]   
    @output nvarchar(250)
AS
BEGIN

    SELECT CAST(@output AS int)
END

Input to the procedure: '1234' I use MultiByteToWideChar() function to convert from UTF8 to UCS2 before passing to the procedure. The output of the MultiByteToWideChar() function is '1111' But when the procedure is executed, I got error as below

Conversion failed when converting the nvarchar value '1234.' to data type int.

It is noted that an extra '.' is appended at the end.

But When we just retrieved the data without cast (SELECT) it has correct value as 1234

I have aallocated (strlen+1)*sizeof(SQLWCHAR) =>(4+1)*2 = 10 bytes to store the resulted wide char string, which is output from MultiByteToWideChar function. Here additional 2 bytes are allocated for null termination.

However, if that additional 2 bytes for null termination is not added then the procedure works fine.

1. Could anyone help me in understanding why the extra bytes causes the problem (though it is 0, it results in '.') ?
2. Whether there is any limitation with CAST to int when using Unicode characters ?

Thanks in advance.

Answer 1

TRY THIS

alter PROCEDURE Test (@YesNo VARCHAR(5) OUTPUT)
AS
BEGIN

    SET NOCOUNT ON;
    SET @YesNo = 1

END
GO


DECLARE @Result VARCHAR(5)

EXEC Test @YesNo = @Result OUTPUT

SELECT @Result