Cari Blog Ini

17 Oktober 2011

Using Nested Cursors To Produce Report Output

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

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;

DECLARE @vendor_id int, @vendor_name nvarchar(50),
    @message varchar(80), @product nvarchar(50);

PRINT '-------- Vendor Products Report --------';



DECLARE vendor_cursor CURSOR FOR
SELECT BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' ';
    SELECT @message = '----- Products From Vendor: ' +
        @vendor_name;

    PRINT @message;

    -- Declare an inner cursor based  
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR
    SELECT v.Name
    FROM Purchasing.ProductVendor AS pv
    INNER JOIN Production.Product AS v
        ON pv.ProductID = v.ProductID AND
           pv.BusinessEntityID = @vendor_id;  -- Variable value from the outer cursor

    OPEN product_cursor;
    FETCH NEXT FROM product_cursor INTO @product;

    IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>' ;   

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product;
        END;

    CLOSE product_cursor;
    DEALLOCATE product_cursor;
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name;

END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

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


Sumber:
http://msdn.microsoft.com/en-us/library/ms180169.aspx