Row-by-Row Processing - SQL Server Query Performance Tuning - page 466

--Calculate money lost per product by processing one product
--at a time
DECLARE @ProductId INT,
@UnitsScrapped SMALLINT,
@ListPrice MONEY;
FETCH NEXT FROM ScrappedProducts INTO @ProductId,@UnitsScrapped,@ListPrice;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MoneyLostPerProduct = @UnitsScrapped * @ListPrice; --Calculate total loss
SET @TotalLoss = @TotalLoss + @MoneyLostPerProduct;
FETCH NEXT FROM ScrappedProducts INTO @ProductId,@UnitsScrapped,
@ListPrice;
END
--Determine status
IF (@TotalLoss > 5000)
SELECT 'We are bankrupt!' AS Status;
ELSE
SELECT 'We are safe!' AS Status;
--Close the cursor and release all resources assigned to the cursor
CLOSE ScrappedProducts;
DEALLOCATE ScrappedProducts;
GO
The stored procedure can be executed as follows, but you should execute it twice to take advantage of plan
caching (see Figure 22-3 ):
EXEC dbo.TotalLoss_CursorBased;