SQL Server: Improve PROCEDURE without using CURSOR

I am looking for a way to write the below procedure without using a CURSOR or just to find a better performing query.

CREATE TABLE #OrderTransaction (OrderTransactionId int, ProductId int, Quantity int);
CREATE TABLE #Product (ProductId int, MediaTypeId int);
CREATE TABLE #OrderDelivery (OrderTransactionId int, MediaTypeId int);

INSERT INTO #Product (ProductId, MediaTypeId) VALUES (1,1);
INSERT INTO #Product (ProductId, MediaTypeId) VALUES (2,2);
INSERT INTO #OrderTransaction(OrderTransactionId, ProductId, Quantity) VALUES (1,1,1);
INSERT INTO #OrderTransaction(OrderTransactionId, ProductId, Quantity) VALUES (2,2,6);

DECLARE @OrderTransactionId int, @MediaTypeId int, @Quantity int; 

DECLARE ordertran CURSOR FAST_FORWARD FOR 
    SELECT  OT.OrderTransactionId, P.MediaTypeId, OT.Quantity
    FROM    #OrderTransaction OT WITH (NOLOCK)
        INNER JOIN  #Product P WITH (NOLOCK)
            ON OT.ProductId = P.ProductId


OPEN ordertran;
FETCH NEXT FROM ordertran INTO @OrderTransactionId, @MediaTypeId, @Quantity;

W开发者_JAVA百科HILE @@FETCH_STATUS = 0 
BEGIN
    WHILE @Quantity > 0 
    BEGIN
        INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
        VALUES (@OrderTransactionId, @MediaTypeId)

        SELECT @Quantity = @Quantity - 1;
    END 

    FETCH NEXT FROM ordertran INTO @OrderTransactionId, @MediaTypeId, @Quantity;
END 

CLOSE ordertran;
DEALLOCATE ordertran;


SELECT  *   FROM    #OrderTransaction 
SELECT  *   FROM    #Product
SELECT  *   FROM    #OrderDelivery

DROP TABLE #OrderTransaction;
DROP TABLE #Product;
DROP TABLE #OrderDelivery;

---

Begin with a Numbers table that is large enough to handle the maximum order amount:

CREATE TABLE Numbers (
   Num int NOT NULL PRIMARY KEY CLUSTERED
)

-- SQL 2000 version
INSERT Numbers VALUES (1)
SET NOCOUNT ON
GO
INSERT Numbers (Num) SELECT Num + (SELECT Max(Num) FROM Numbers) FROM Numbers
GO 15

-- SQL 2005 and up version
WITH
   L0 AS (SELECT c = 1 UNION ALL SELECT 1),
   L1 AS (SELECT c = 1 FROM L0 A, L0 B),
   L2 AS (SELECT c = 1 FROM L1 A, L1 B),
   L3 AS (SELECT c = 1 FROM L2 A, L2 B),
   L4 AS (SELECT c = 1 FROM L3 A, L3 B),
   L5 AS (SELECT c = 1 FROM L4 A, L4 B),
   N AS (SELECT Num = ROW_NUMBER() OVER (ORDER BY c) FROM L5)
INSERT Numbers(Num)
SELECT Num FROM N
WHERE Num <= 32768;

Then, immediately after your INSERT statements:

INSERT #OrderDelivery (OrderTransactionId, MediaTypeId)
SELECT
   OT.OrderTransactionId,
   P.MediaTypeId
FROM
   #OrderTransaction OT
   INNER JOIN #Product P ON OT.ProductId = P.ProductId
   INNER JOIN Numbers N ON N.Num BETWEEN 1 AND OT.Quantity

That should do it!

If for some reason you have qualms about putting a permanent Numbers table in your database (which I don't understand as it is a wonderful tool), then you can simply join to the CTE given instead of the table itself. In SQL 2000 you can create a temp table and use a loop, but I would advise against this strongly.

A Numbers table is highly recommended. There is no concern about some future change breaking it (the set of whole numbers won't change any time soon). Some people use a Numbers table with a million numbers in it, which is only around 4MB of storage.

To answer critics of the Numbers table: if the database design uses a numbers table, then that table won't need to change. It is like any other table in the database and can be relied on. You don't worry too much about queries against an Orders table failing because some day the table might not exist, so I don't see why there would be any similar concern about another table that is required and depended on.

UPDATE

In the time since writing this answer I have learned about the master.dbo.spt_values table which has a number column. When queried with where type='P' you get 0 - 255 in SQL 2000 and 0 - 8191 in SQL 2005 and up. (There are also potentially useful low and high columns.) You can cross join this table to itself a couple of times if necessary to get, even in SQL 2000, a bunch of rows very quickly.

---

The trick is to introduce a table of values (named, in the example below, MyTableOfIntegers) which contains all the integer values between 1 and (at least) some value (in the case at hand, that would be the biggest possible Quantity value from OrderTransaction table).

INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
  SELECT  OT.OrderTransactionId, P.MediaTypeId
  FROM  #OrderTransaction OT WITH (NOLOCK)
  INNER JOIN  #Product P WITH (NOLOCK)
    ON OT.ProductId = P.ProductId
  JOIN MyTableOfIntegers I ON I.Num <= OT.Quantity
  --WHERE some optional conditions 

Essentially the extra JOIN on MyTableOfIntegers, produces as many duplicate rows as OT.Quantity, and that seems to be what the purpose of the cursor was: to insert that many duplicated rows in the OrderDelivery table.

I didn't check the rest of the logic with the temporary tables and all (I'm assuming these are temp tables for the purpose of checking the logic rather than being part of the process proper), but it seems that the above is the type of construct needed to express the needed logic in declarative fashion only, without any cursor or even any loop.

---

Here is a slight variation on the previous answers, that avoids a permanent numbers table (though I am not sure why people are so afraid of this construct), and allows you to build a run-time CTE that contains exactly the set of numbers you'll need to perform the correct number of inserts (by checking for the highest quantity). I commented out the CROSS JOIN in the initial CTE, but you can use it if your quantity for any given order can exceed the number of rows in sys.columns. Hopefully that is an unlikely scenario. Note that this is for SQL Server 2005 and up ... it is always useful to let us know which specific version(s) you are targeting.

DECLARE @numsNeeded INT;

SELECT @numsNeeded = MAX(Quantity) FROM #OrderTransaction;

WITH n AS 
(
    SELECT TOP (@numsNeeded) i = ROW_NUMBER()
    OVER (ORDER BY c.[object_id])
    FROM sys.columns AS c  --CROSS JOIN sys.columns AS c2
)
INSERT #OrderDelivery
(
    OrderTransactionID,
    MediaTypeID
)
SELECT t.OrderTransactionID, p.MediaTypeID
    FROM #OrderTransaction AS t
    INNER JOIN #Product AS p
    ON t.ProductID = p.ProductID
    INNER JOIN n
    ON n.i <= t.Quantity;

---

INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
SELECT  OT.OrderTransactionId, P.MediaTypeId,
FROM    #OrderTransaction OT
INNER JOIN  #Product P
ON OT.ProductId = P.ProductId
WHERE OT.Quantity > 0

I feel like i'm misreading the logic here, but isn't that the equivelant?

---

This still uses a loop but it has gotten rid of the cursor. Short of creating a table of numbers to join on, I think this is the best answer.

DECLARE @Count AS INTEGER

SET @Count = 1

WHILE (1 = 1)
BEGIN

    INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId]) 
    SELECT  OT.OrderTransactionId, P.MediaTypeId, OT.Quantity 
    FROM    #OrderTransaction OT WITH (NOLOCK) 
        INNER JOIN  #Product P WITH (NOLOCK) 
            ON OT.ProductId = P.ProductId 
    WHERE OT.Quantity > @Count

    IF @@ROWCOUNT = 0 
        BREAK

    SET @COUNT = @COUNT + 1
END