Where am I going wrong with this SQL query?

Question

I am attempting to do the following:

1. Check to see if the table does not exist and if so, create the TABLE 'tmpTriangleTransfer'.
2. Check to see if the table exists and if so, DROP the TABLE 'tmpTriangleTransfer'.
3. Insert the data being pulled from the other tables into the 2nd - 5th columns of the TABLE 'tmpTriangleTransfer'.
4. Loop and for each row that exists in the TABLE 'tmpTriangleTransfer' update the 1st column with the declared information.
5. Return all of the information from that table (to be formatted into a report).

Can someone please help me figure out what I am doing wrong? I'm getting no results even though I know for a fact there are records (when I run just the SELECT statement on the last line, it shows records and when I run the SELECT DISTINCT statement in the middle, it shows the same records).

IF OBJECT_ID('tmpTriangleTransfer') IS NOT NULL
 DROP TABLE tmpTriangleTransfer;
IF OBJECT_ID('tmpTriangleTransfer') IS NULL

CREATE TABLE tmpTriangleTransfer
            (
            CompanyName varchar(max),
            OrderID decimal(19,2) NULL,
            DriverID int NULL,
            VehicleID int NULL,
            Phone varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            BOL varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            );

INSERT INTO tmpTriangleTransfer (OrderID, BOL, DriverID, VehicleID, Phone)
SELECT DISTINCT tblOrder.OrderID AS OrderID, tblOrder.BOL AS BOL, tblOrderDrivers.DriverID AS DriverID, tblDrivers.VehicleID AS VehicleID, tblWorker.Phone AS Phone
    FROM tblOrder WITH (NOLOCK)
        INNER JOIN tblActiveOrders
        ON tblOrder.OrderID = tblActiveOrders.OrderID
        INNER JOIN tblOrderDrivers
        ON tblOrder.OrderID = tblOrderDrivers.OrderID
        INNER JOIN tblDrivers
        ON tblOrderDrivers.DriverID = tblDrivers.DriverID
        INNER JOIN tblWorker
        ON tblDrivers.WorkerID = tblWorker.WorkerID
        WHERE tblOrder.CustID = 7317
        ORDER BY tblOrder.OrderID`


DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(OrderID) FROM tmpTriangleTransfer)

DECLARE @Iter INT
SET @Iter = (SELECT MIN(OrderID) FROM tmpTriangleTransfer)

WHILE @Iter <= @MaxRownum
BEGIN

   UPDATE tmpTriangleTransfer
      SET tmpTriangleTransfer.CompanyName = 'Triangle'
   WHERE tmpTriangleTransfer.CompanyName IS NULL;

   SET @Iter = @Iter + 1

END

SELECT * from tmpTriangleTransfer WITH (NOLOCK)

Answer 1

Taken from a combination of the suggestion from Dai and the requirements of my employer:

`SELECT 'Triangle' AS CompanyName, tblOrder.OrderId AS OrderID, tblOrder.BOL AS BOL, tblOrderDrivers.DriverID AS DriverID, tblDrivers.VehicleID AS VehicleID, tblWorker.Phone AS Phone
FROM tblOrder WITH (NOLOCK)
        INNER JOIN tblActiveOrders WITH (NOLOCK)
        ON tblOrder.OrderID = tblActiveOrders.OrderID
    INNER JOIN tblOrderDrivers WITH (NOLOCK)
        ON tblOrder.OrderID = tblOrderDrivers.OrderID
    INNER JOIN tblDrivers WITH (NOLOCK)
        ON tblOrderDrivers.DriverID = tblDrivers.DriverID
    INNER JOIN tblWorker WITH (NOLOCK)
        ON tblDrivers.WorkerID = tblWorker.WorkerID
WHERE
    tblOrder.CustID = 7317
ORDER BY
    tblOrder.OrderID desc`

Answer 2

Your existing query is far too complicated. In fact, you don't need a temporary table, the WHILE loop, or anything - just a single SELECT is all you need:

SELECT
    'Triangle' AS CompanyName,
    tblOrder.OrderId,
    tblOrder.BOL,
    tblOrderOrders.DriverID,
    tblDrivers.VehicleID,
    tblWorker.Phone
FROM
    tblOrder
    OUTER JOIN tblActiveOrders ON tblOrder.OrderID = tblActiveOrders.OrderID
    OUTER JOIN tblOrderDrivers ON tblOrder.OrderID = tblOrderDrivers.OrderID
    OUTER JOIN tblDrivers      ON tblOrderDrivers.DriverID = tblDrivers.DriverID
    OUTER JOIN tblWorker       ON tblDrivers.WorkerID = tblWorker.WorkerID
WHERE
    tblOrder.CustID = 7317
ORDER BY
    tblOrder.OrderID

• I've changed your query to use OUTER JOIN instead of INNER JOIN because I suspect this is the main reason for no data being returned. INNER JOIN requires rows to exist in both tables (relations) and I suspect that you have Orders without Drivers or that not every Order is in ActiveOrders. Change the joins to INNER JOIN if you know that related rows will always be present.
• You can return literals in queries directly, like I'm doing in the SELECT 'Triangle' AS CompanyName part, whereas you were seemingly manually adding it to the output temporary-table.
• Your code didn't seem to be doing anything that would require the WITH (NOLOCK) modifier - the fact it was repeated everywhere makes it look like a case of Cargo-Cult Programming.
• Tip: In SQL, a SELECT statement, as written, is not representative of its logical execution order. It should instead be read in this order: FROM > WHERE > [GROUP BY >] SELECT > ORDER BY.
  • This is why in .NET Linq the .Select() call is often at the end, not the beginning, because previous Linq expressions define the data sources.
• This query can be parameterised by converting it to a Table-defined Function that accepts CustID as a parameter, I also assume you have the company name "Triangle" stored in a table somewhere - embedding it as a literal value for a single query is a code-smell - what's so special about 7317 / "Triangle"?
  • Related note: Generally speaking, queries that only SELECT data (and don't perform any INSERT/UPDATE/DELETE/ALTER/CREATE statements) should be Table-valued UDFs or Views and not Stored Procedures - so that they can benefit from function-composition, query-composition and runtime execution plan optimizations that you cannot get with Stored Procedures.
• If you're able to, see if you can remove the tbl prefix from the table names (Using "tbl" as a prefix has its defenders, but my own personal opinion is that it's an obsolete developer aid as today's database tooling shows type information, and it makes database refactoring harder (e.g. converting a table to a view).