TinTin (la********@yahoo.com) writes:
Erland: I am not too sure about what you mean by "work set-based".
There might be an easier alternative to what I am doing. Could you
precisely refer to me a specific area; or topic which I should read?
Rather than writing:
DECLARE @price money,
@qty int,
@total money,
@orderid int,
@prev_orderid int
DECLARE order_total_cur INSENSITIVE CURSOR FOR
SELECT orderid, price, qty
FROM order_details
ORDER BY orderid
OPEN order_total_cur
SELECT @total = 0
WHILE 1 = 1
BEGIN
FETCH order_total_cur INTO @orderid, @price, @qty
IF @@fetch_status <> 0
BREAK
IF @prev_orderid IS NOT NULL AND @orderid <> @prev_orderid
BEGIN
UPDATE orders
SET total = @total
WHERE orderid = @prev_orderid
SELECT @total = 0
END
SELECT @total = @total + @price * @qty, @prev_orderid = @orderid
END
DEALLOCATE order_total_cur
IF @orderid IS NOT NULL
BEGIN
UPDATE orders
SET total = @total
WHERE orderid = @orderid
END
You write:
UPDATE orders
SET total = od.total
FROM orders o
JOIN (SELECT orderid, total = sum(qty * price)
FROM orderdetails
GROUP BY orderid) AS od ON o.orderid = od.orderid
Not only is this more concise and less error-prone to write, the
difference in performance could be magnirute if there are many
rows in the table.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
