|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
to generate a list of product codes for selected purchase orders. Note that cursors are not scalable, so performance issues should be taken into account.
USE AdventureWorks;
SET NOCOUNT ON
DECLARE @PurchaseList TABLE
(POID INT,
ProductList VARCHAR(4096))
DECLARE @PurchaseOrderID INT, @ProductCode VARCHAR(12),
@ProductList VARCHAR(4096)
-- Declare a cursor for purchase orders
DECLARE curOrders CURSOR FOR
SELECT PurchaseOrderID
FROM Purchasing.PurchaseOrderHeader
WHERE YEAR(OrderDate)= 2003
AND MONTH(OrderDate)= 12
-- Loop through each purchase order in the selected range
OPEN curOrders
FETCH NEXT FROM curOrders INTO @PurchaseOrderID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ProductList = ''
-- Inner cursor for product list for purchase order
DECLARE curProducts CURSOR FOR
SELECT p.productNumber FROM Purchasing.PurchaseOrderDetail pd
INNER JOIN Production.Product p
ON pd.ProductID = p.ProductID
WHERE pd.PurchaseOrderID = @PurchaseOrderID
-- Loop through the products on the purchase order
OPEN curProducts
FETCH NEXT FROM curProducts INTO @ProductCode
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Build the csv list via string concatenation
SET @ProductList = @ProductList + @ProductCode + ', '
FETCH NEXT FROM curProducts INTO @ProductCode
END
CLOSE curProducts
DEALLOCATE curProducts
-- Insert product list into table variable
INSERT INTO @PurchaseList
VALUES (@PurchaseOrderID, @ProductList)
FETCH NEXT FROM curOrders INTO @PurchaseOrderID
END
CLOSE curOrders
DEALLOCATE curOrders
-- Remove trailing commas
UPDATE @PurchaseList
SET ProductList = LEFT(RTRIM(ProductList), LEN(RTRIM(ProductList))-1)
WHERE RIGHT(RTRIM(ProductList),1)=','
-- Publish results
SELECT * FROM @PurchaseList
ORDER BY POID
|
