--------------------------------------------------------------------------
---------------------------------------------------------------------------
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