The Locals window is a debugger panel for watching local variables and parameters while execution is paused. Step into a stored procedure with F11, then observe values changing after each query. section 7.3
Divide-by-zero in CalculateCampaignSummary
Stepping through CalculateCampaignSummary reveals that @AvgCPM is 0 immediately before @AvgBidPrice is divided by it. Checking the Immediate window shows that the source query returns 42 rows, unintentionally assigning multiple values to variables. Correcting the query to return averages fixes the procedure. section 7.3
Use the window as a short diagnostic routine:
- Start execution with the debugger and pause at the procedure. section 7.3
- Choose Step Into and inspect variables after each query. section 7.3
- Confirm suspicious query results with the Immediate window before changing code. section 7.3
Listing 7.13 Creating a CalculateCampaignSummary stored procedure
CREATE PROCEDURE dbo.CalculateCampaignSummary
@CampaignID INT,
@Budget MONEY
AS
BEGIN
DECLARE @AvgCPM DECIMAL ;
DECLARE @CampaignCost MONEY ;
DECLARE @NoOfImp INT ;
DECLARE @AvgBidPrice MONEY ;
DECLARE @CostOfBidPerc DECIMAL ;
DECLARE @BudgetDifference MONEY ;
SELECT
@NoOfImp = COUNT(*)
, @CampaignCost = (SUM(CostPerMille) / 1000) * COUNT(*)
FROM marketing.Impressions
WHERE CampaignID = @CampaignID
GROUP BY CampaignID ;
SELECT
@avgcpm = AvgCostPerMille
, @avgbidprice = AvgBidPrice
FROM reporting.ImpressionAggregates
WHERE CampaignID = @CampaignID ;
SET @CostOfBidPerc = (@AvgBidPrice / @AvgCPM) * 100 ;
SET @BudgetDifference = @budget - @CampaignCost ;
SELECT
@CampaignID CampaignID
, @AvgCPM AvgCPM
, @CampaignCost CampaignCost
, @NoOfImp ImpQty
, @CostOfBidPerc CostToBidPercentage
, @BudgetDifference ;
SELECT DISTINCT
ReferralURL
, RenderingID
FROM marketing.Impressions
WHERE CampaignID = @CampaignID ;
END
Inspect the stored procedure whose variables are examined during the debugging walkthrough. section 7.3
