SQL Server 查询性能 : Nested cursors

Question

我有一个存储过程，它选择一些 1-n 关系和相关数据作为 XML 数据引用到列。

目的是返回一条记录，它是 1-n 关系，作为带有额外数据列的一条记录，这是通过将这些相关数据添加为 XML 来完成的。

引用表:(表A)

ID   NAME     VALUE
---------------------
1   Sepehr    1000    
2   Sarah     1001

相关表:(表 B)

ID   Value  FK_Value   ORDER    TITLE
-------------------------------------
1     A      1000         1      t1
2     B      1000         2      t2
3     C      1000         3      t3

我想得到这个输出:

ID   NAME    FK_Value   Attribs
-----------------------------------------------------
1   Sepehr    1000       <XML><ID>1</ID><ID>2</ID><ID>3</ID></XML>
2   Sarah     1001       null

实际上我希望创建一个 View 来执行此操作，但我做不到，有人告诉我使用 View 是不可能的。

最后这是我写的存储过程——这是正确的方法还是有其他方法？

DECLARE @T1 table (A_ID int,Attribs XML)   

DECLARE db_cursorLegendRowsValues CURSOR FOR 
    SELECT ID, VALUE 
    FROM A

OPEN db_cursorLegendRowsValues 

FETCH NEXT FROM db_cursorLegendRowsValues INTO @loop_ID, @loop_VALUE

WHILE @@FETCH_STATUS = 0    
BEGIN   
    DECLARE db_cursorInternal CURSOR FOR 
        SELECT TITLE, ORDER 
        FROM B 
        WHERE FK_Value =  @loop_VALUE

    OPEN db_cursorInternal 

    FETCH NEXT FROM db_cursorInternal INTO @tmpTitle, @ORDER

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @querySelect = @querySelect + ', MAX(CASE WHEN order = ' + cast(@ORDER as nvarchar(max)) + ' THEN value END) AS [' +REPLACE (@tmpTitle,' ','_') + '] '

        FETCH NEXT FROM db_cursorInternal INTO @tmpTitle, @ORDER
    END 

    CLOSE db_cursorInternal 
    DEALLOCATE db_cursorInternal

    SET @query = 
        ' SELECT ' +  cast(@loop_ID as nvarchar(max))  +',(
                            SELECT A.Value,  
        '
    SET @query = @query + STUFF(@querySelect,1,1,'') + ' FROM   A 
        WHERE [A.Value] = ' + cast(@loop_VALUE  as nvarchar(max)) + '
        FOR XML RAW (''Item''), root (''Items'') , ELEMENTS XSINIL )'

    SET @querySelect = ''
    --PRINT(@query) 

    INSERT into @T1  execute (@query )

    FETCH NEXT FROM db_cursorLegendRowsValues INTO @loop_ID, @loop_VALUE
END

CLOSE db_cursorLegendRowsValues 
DEALLOCATE db_cursorLegendRowsValues

SELECT * FROM @T1

Answer 1

所有内容都可以浓缩成几行，根本不需要游标。这将在 View 中可用:

DECLARE @tblA TABLE(ID INT IDENTITY,NAME VARCHAR(100),VALUE INT);
INSERT INTO @tblA VALUES
 ('Sepehr',1000)    
,('Sarah',1001);

DECLARE @tblB TABLE(ID INT IDENTITY,Value VARCHAR(100),FK_Value INT,[ORDER] INT,TITLE VARCHAR(100));
INSERT INTO @tblB VALUES
 ('A',1000,1,'t1')
,('B',1000,2,'t2')
,('C',1000,3,'t3');

SELECT a.*
      ,(SELECT ID FROM @tblB AS b WHERE b.FK_Value=a.VALUE FOR XML PATH(''),ROOT('XML'),TYPE) AS Attribs
FROM @tblA AS a

结果

ID  NAME    VALUE   Attribs
1   Sepehr  1000    <XML><ID>1</ID><ID>2</ID><ID>3</ID></XML>
2   Sarah   1001    NULL