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(MAX),' + @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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

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
1
2
3
4
5
6
7