SQLUSA
Free Trial Save up to 50% on Combos

Microsoft SQL Server 2008 Best Practices

How to apply nested cursors and XML path?

Execute the following SQL Server 2008 T-SQL script in SSMS Query Editor to generate a book title - author junction table representing many-to-many relationship. XML Path is used to generate a list of authors for books with multiple authors.

-- T-SQL nested cursors demonstration

USE pubs;

SET nocount  ON

DECLARE  @BookTitle VARCHAR(16),

         @AuthorID  VARCHAR(32)

DECLARE  @Result  TABLE(

                        Title  VARCHAR(128),

                        AuthorID VARCHAR(32)

                        )

-- Outer cursor

DECLARE curBookTitle CURSOR  FOR

SELECT   title_id

FROM     titles

ORDER BY title_id

OPEN curBookTitle

FETCH  curBookTitle

INTO @BookTitle

WHILE (@@FETCH_STATUS = 0)

  BEGIN

    PRINT 'OUTER LOOP'

    -- Inner nested cursor

    DECLARE curTitleAuthor CURSOR  FOR

    SELECT au_id

    FROM   titleauthor

    WHERE  title_id = @BookTitle

    OPEN curTitleAuthor

    FETCH  curTitleAuthor

    INTO @AuthorID

    WHILE (@@FETCH_STATUS = 0)

      BEGIN

        PRINT ' INNER LOOP'

        

        INSERT @Result

        SELECT @BookTitle,

               @AuthorID

        FETCH  curTitleAuthor

        INTO @AuthorID

      END

    CLOSE curTitleAuthor

    DEALLOCATE curTitleAuthor

    FETCH  curBookTitle

    INTO @BookTitle

  END

CLOSE curBookTitle

DEALLOCATE curBookTitle

 

-- Junction table simple listing

SELECT *

FROM     @Result

ORDER BY Title,

         AuthorID

/*

Title       AuthorID

BU1032      213-46-8915

BU1032      409-56-7008

BU1111      267-41-2394

BU1111      724-80-9391

......

*/

 

-- Books with multiple authors listed on one line

-- XML path - text() will generate a comma separated list

SELECT DISTINCT Title,

                AuthorList = STUFF((SELECT   ', ' + AuthorID AS [text()]

                                    FROM     @Result r

                                    WHERE    r.Title = r1.Title

                                    ORDER BY AuthorID

                                    for XML Path ('')),1,1,'')

FROM   @Result r1

GO

/*

Title       AuthorList

BU1032      213-46-8915, 409-56-7008

BU1111      267-41-2394, 724-80-9391

BU2075      213-46-8915

BU7832      274-80-9391

MC2222      712-45-1867

MC3021      722-51-5454, 899-46-2035

.....

*/

------------

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page