Why EXEC sp_sqlexec @strSQL with EXECUTE (PRINT @strSQL) is different

Question

I have this query:

CREATE PROCEDURE [dbo].[TraCuuBoTieuChi_Head]
    @ChuoiLoai nvarchar(20),
    @ChuoiXa nvarchar(MAX),
    @ThangNam datetime
AS
    DECLARE @strSQL nvarchar(MAX)
    DECLARE @Str NVARCHAR(MAX)
    DECLARE @Str2 NVARCHAR(MAX)
    DECLARE @Str3 NVARCHAR(MAX)
    DECLARE @Str4 NVARCHAR(MAX)
    DECLARE @Str5 NVARCHAR(MAX)
    DECLARE @Str6 NVARCHAR(MAX)

    SET @Str = STUFF((SELECT ',' + (CASE WHEN items = 1 THEN 'SL_CuThe_Loai1 nvarchar(50)' WHEN items = 2 THEN 'SL_CuThe_Loai2 nvarchar(50)' ELSE 'SL_CuThe_Loai3 nvarchar(50)' END) FROM dbo.Split(@ChuoiLoai, ',') FOR XML PATH('')), 1, 1, '')
    SET @Str2 = STUFF((SELECT ',' + (CASE WHEN items = 1 THEN 'SL_CuThe_Loai1' WHEN items = 2 THEN 'SL_CuThe_Loai2' ELSE 'SL_CuThe_Loai3' END) FROM dbo.Split(@ChuoiLoai, ',') FOR XML PATH('')), 1, 1, '')
    SET @Str3 = STUFF((SELECT ',' + (CASE WHEN items = 1 THEN N'N''Giá trị mẫu 1''' WHEN items = 2 THEN N'N''Giá trị mẫu 2''' ELSE N'N''Giá trị mẫu 3''' END) FROM dbo.Split(@ChuoiLoai, ',') FOR XML PATH('')), 1, 1, '')

    PRINT @Str3

    SET @Str4 = STUFF((SELECT ',' + ('Xa' + items + ' nvarchar(100)') FROM dbo.Split(@ChuoiXa, ',') FOR XML PATH('')), 1, 1, '')
    SET @Str5 = STUFF((SELECT ',' + ('Xa' + items ) FROM dbo.Split(@ChuoiXa, ',') FOR XML PATH('')), 1, 1, '')
    SET @Str6 = STUFF((SELECT ',' + ((SELECT 'N''' + LocationName + '''' FROM Location WHERE LocationID = items)) FROM dbo.Split(@ChuoiXa, ',') FOR XML PATH('')), 1, 1, '')

    PRINT @Str6

    SET @strSQL = 'CREATE TABLE #tmp_paging_index (BulletCode nvarchar(30),TenTieuChi nvarchar(50),' + @Str  + ',' +  @Str4 + ')

    INSERT INTO #tmp_paging_index (BulletCode,TenTieuChi,' + @Str2  + ',' +  @Str5 + ')
        SELECT N''BulletCode'',N''Tên tiêu chí'',' + @Str3  + ',' +  @Str6 + '

    SELECT * FROM #tmp_paging_index
    DROP TABLE #tmp_paging_index'

    PRINT @strSQL

    EXEC sp_sqlexec @strSQL

When I run the procedure it outputs the following:

The Print section produces the following output:

*PRINT 1 :  N'Giá trị mẫu 1',N'Giá trị mẫu 2',N'Giá trị mẫu 3'
PRINT 2: N'Xã Phú Hải',N'Xã Quảng Trung ',N'Xã Quảng Phong '
PRINT 3: CREATE TABLE #tmp_paging_index (BulletCode nvarchar(30),TenTieuChi nvarchar(MAX),SL_CuThe_Loai1 nvarchar(50),SL_CuThe_Loai2 nvarchar(50),SL_CuThe_Loai3 nvarchar(50),Xa14 nvarchar(100),Xa15 nvarchar(100),Xa16 nvarchar(100))
INSERT INTO #tmp_paging_index (BulletCode,TenTieuChi,SL_CuThe_Loai1,SL_CuThe_Loai2,SL_CuThe_Loai3,Xa14,Xa15,Xa16)
SELECT N'BulletCode',N'Tên tiêu chí',N'Giá trị mẫu 1',N'Giá trị mẫu 2',N'Giá trị mẫu 3',N'Xã Phú Hải',N'Xã Quảng Trung ',N'Xã Quảng Phong '
SELECT * FROM #tmp_paging_index
DROP TABLE #tmp_paging_index*

I run the print version and it came out different:

Now I have to do the same thing as running the print.

Answer 1

sp_sqlexec is from much earlier days. Not sure it is still supported on later version of SQL Server. You should use sp_executesql.

I found this thread on MSDN that mention sp_sqlexec does not support unicode. Probably that explain why you get different output

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/554d6373-8a22-4b10-8744-df13e5415f84/spexecutesql-vs-spsqlexec?forum=sqlgetstarted