Last active
March 10, 2019 12:19
-
-
Save ezhov-da/cffda81e09aaf6978169947afd428845 to your computer and use it in GitHub Desktop.
sql mssql курсор
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment