SQL 2005+

SQL Server Temporary Tables (2)

The fifty-seventh part of the SQL Server Programming Fundamentals tutorial investigates temporary tables. Temporary tables can be used to hold information during long-running or complex operations without locking live data rows.

Populating the Initial Data

To initialise the data in the temporary table we will copy all of the customer numbers from the live data. All other columns in the will contain null values at this point. We can copy the customer numbers using an INSERT statement that sources its information from a query as follows:

INSERT INTO #CustomerValue (CustomerNumber)
SELECT CustomerNumber FROM Customers

Declaring the Cursor

The next step is to populate the contract value and costs columns. To achieve this we will use a cursor to allow us to process the data one row at a time. The cursor is simple, being based upon selecting the only populated column from the temporary table with no filtering of rows.

Add the following to the stored procedure to declare the cursor.

DECLARE CustomerCursor CURSOR FOR
SELECT CustomerNumber FROM #CustomerValue
OPEN CustomerCursor

To open the cursor and define the start of a WHILE loop, add the following code:

FETCH NEXT FROM CustomerCursor
INTO @CustomerNumber

WHILE @@FETCH_STATUS = 0
BEGIN

Populating the Contract Value and Costs

Inside the loop we need to retrieve the monetary values for the contract value, engineer cost and parts cost. Each of these will be held in a variable that will be used in a subsequent UPDATE statement to apply the values to the appropriate columns in the temporary table. Note that the UPDATE statement checks to see if any of the values is null. If any are, they are replaced with zeroes.

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

We can now end the loop and close and deallocate the cursor:

    FETCH NEXT FROM CustomerCursor
    INTO @CustomerNumber
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor