sql nested select

 How to declare a nested cursor in stored procedure using SQL  
 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 VendorID, Name  
 FROM Purchasing.Vendor  
 WHERE PreferredVendorStatus = 1  
 ORDER BY VendorID;  
 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 pv, Production.Product v  
   WHERE pv.ProductID = v.ProductID AND  
   pv.VendorID = @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;  
 simpl of How to declare a cursor in stored procedure using SQL server  
 for more details visit   
 http://scriptquery.blogspot.in/  
 http://scriptquery.blogspot.in/  

11:45 PM Share: