Updating the GetProfitPerCustomer Stored Procedure
In the previous article in this series we created a stored procedure that used a cursor and a temporary table whilst determining the profit generated for each customer in the JoBS database. This stored procedure could have used a table variable instead of the temporary table. To change the stored procedure to use a table variable would require several minor modifications:
- The creation of the temporary table would be replaced with the declaration of a table variable, "@CustomerValue".
- All references to #CustomerValue would be changed to @CustomerValue.
- The commands used to drop the temporary table would no longer be needed.
To modify the stored procedure, you can execute the following script.
ALTER PROCEDURE GetProfitPerCustomer AS
DECLARE @CustomerNumber INT
DECLARE @TotalContractValue MONEY
DECLARE @EngineerCost MONEY
DECLARE @PartsCost MONEY
DECLARE @CustomerValue TABLE
(
CustomerNumber INT,
TotalContractValue MONEY,
EngineerCost MONEY,
PartsCost MONEY,
Profit MONEY
)
INSERT INTO @CustomerValue (CustomerNumber)
SELECT CustomerNumber FROM Customers
DECLARE CustomerCursor CURSOR FOR
SELECT CustomerNumber FROM @CustomerValue
OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor
INTO @CustomerNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TotalContractValue = sum(ContractValue) FROM Contracts C
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber
SELECT @EngineerCost = sum(J.Duration * E. HourlyRate) FROM Jobs J
INNER JOIN Engineers E ON J.EngineerId = E.EngineerId
INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber
SELECT @PartsCost = sum(TotalCost) FROM UsedParts P
INNER JOIN Jobs J ON P.JobId = J.JobId
INNER JOIN Contracts C ON J.ContractNumber = C.ContractNumber
INNER JOIN CustomerAddresses A ON C.CustomerAddressId = A.AddressId
WHERE A.CustomerNumber = @CustomerNumber
UPDATE
@CustomerValue
SET
TotalContractValue = isnull(@TotalContractValue, 0),
EngineerCost = isnull(@EngineerCost, 0),
PartsCost = isnull(@PartsCost, 0)
WHERE
CustomerNumber = @CustomerNumber
FETCH NEXT FROM CustomerCursor
INTO @CustomerNumber
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor
UPDATE
@CustomerValue
SET
Profit = TotalContractValue - EngineerCost - PartsCost
SELECT * FROM @CustomerValue ORDER BY CustomerNumber6 February 2010