SQL Server query performance: Nested cursors

Question

I have a stored procedure which is selecting some 1-n relations and related data to the referenced column as XML data.

The purpose is to return a record and it's 1-n relations as ONE record with extra data columns, this is done using adding these related data as XML.

Reference table: (TABLE A)

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

Related table: (TABLE B)

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

I want to get this output:

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

Actually I was hoping to create a view to do this, but I couldn't and someone told me its not possible using views.

Finally this is the stored procedure I have written - is this a correct approach or are there any other ways?

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

Is it not returning desired results? If you are here just to find efficient approach to do this then Code Review is better place for such type of quesitons. — mmushtaq
– mmushtaq, Commented Apr 10, 2017 at 5:55
yes I am trying to find efficient approach. I didnt know about Code Review, Thank you — Sepehr Davarnia
– Sepehr Davarnia, Commented Apr 11, 2017 at 7:53

Gottfried Lesigang · Accepted Answer · 2017-04-10 07:15:40Z

1

The whole lot can be condensed to a few lines, no cursors needed at all. This wil be useable in a 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

The result

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

edited Apr 10, 2017 at 7:15

answered Apr 10, 2017 at 7:08

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sepehr Davarnia Over a year ago

This is working as it should. The problem here is i have to get name of xml elements from another table. So I Think i have to use dynamic queries which is not possible using Views

Gottfried Lesigang Over a year ago

@SepehrDavarnia Please avoid chameleon questions... In your question you provide sample data and expected output. Both answers provide a (quite similar) solution for this... Now you say that The problem here is i have to get name of xml elements from another table. I have no idea what you mean... One principle on SO is: One issue, one question. I'd ask you to vote on the existing answers, choose one of them to accept it as solution and start a new question, where you provide more information, sample and expected output for this new issue. Thx

Sepehr Davarnia Over a year ago

Hi Shnugo. Thank you for your advise. I have asked my question in this way cause I thought the answer may solve the problem. And also I have provided my own query to show exact things. Thank you anyway

BeanFrog · Accepted Answer · 2017-04-10 07:04:49Z

1

It is possible to do this with just one query - you can use a subquery with your select as follows:

select id
    , name
    , value as fk_value
    , (select id from @table_b b
       where a.value = b.fk_value
       for xml path (''), root ('xml'))
from @table_a a

answered Apr 10, 2017 at 7:04

BeanFrog

2,32514 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Server query performance: Nested cursors

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related