quote:

---

Originally posted by Shastryv

But this guy wants to implement in a CURSOR reading row by row and update each row.

---

IF @GuyIsBoss
    EXEC WriteCursor
  ELSE
    EXEC DoItTheRightWay

quote:

---

Ask him if he'd prefer an nested curosr...place all of the values of an in clause...wait I have to write this one...

---

quote:

---

Originally posted by nr

The cursor might not be slower...

---

quote:

---

but it will restrict the changes you can make in the future.

---

quote:

---

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

USE Northwind
GO

-- Just in Case
SELECT * INTO Orders_bkp FROM Orders

-- Lets Build the In Value Tables
SET NOCOUNT ON
CREATE TABLE EmpIn (EmployeeId int)
CREATE TABLE CustIn (CustomerId nchar(10))
GO

   INSERT INTO EmpIn(EmployeeId)
	SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4

   INSERT INTO CustIn(CustomerId)
	SELECT 'ALFKI' UNION ALL
	SELECT 'ANATR' UNION ALL
	SELECT 'ANTON' UNION ALL
	SELECT 'AROUT'
GO

-- Let's smoke some cpu's

DECLARE @FETCH_STATUS99 int, @OrderId int, @EmployeeId int, @CustomerId nchar(10)

DECLARE myCursor99 CURSOR FOR SELECT OrderId    FROM Orders
DECLARE myCursor98 CURSOR FOR SELECT EmployeeId FROM EmpIn
DECLARE myCursor97 CURSOR FOR SELECT CustomerId FROM CustIn

SELECT GetDate() AS systime, 'Starting Cursor Processing' AS sysmessage

OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @OrderId
SET @FETCH_STATUS99 = @@FETCH_STATUS

WHILE @FETCH_STATUS99 = 0 
  BEGIN
	OPEN myCursor98
	FETCH NEXT FROM myCursor98 INTO @EmployeeId
	WHILE @@FETCH_STATUS = 0
	  BEGIN
		IF EXISTS (SELECT * FROM Orders WHERE OrderId = @OrderID AND EmployeeId = @EmployeeId)
		  UPDATE Orders SET EmployeeId = @EmployeeId WHERE OrderId = @OrderId
		FETCH NEXT FROM myCursor98 INTO @EmployeeId
	  END
	CLOSE myCursor98

	OPEN myCursor97
	FETCH NEXT FROM myCursor97 INTO @CustomerId
	WHILE @@FETCH_STATUS = 0
	  BEGIN
		IF EXISTS (SELECT * FROM Orders WHERE OrderId = @OrderID AND CustomerId = @CustomerId)
		  UPDATE Orders SET CustomerId = @CustomerId WHERE OrderId = @OrderId
		FETCH NEXT FROM myCursor97 INTO @CustomerId
	  END
	CLOSE myCursor97
	FETCH NEXT FROM myCursor99 INTO @OrderId  
	SET @FETCH_STATUS99 = @@FETCH_STATUS
  END
CLOSE myCursor99

DEALLOCATE myCursor99
DEALLOCATE myCursor98
DEALLOCATE myCursor97

SELECT GetDate() AS systime, 'Endind Cursor Processing' AS sysmessage

-- And they way it should be done...

SELECT GetDate() AS systime, 'Starting Set Processing' AS sysmessage
  
UPDATE Orders SET EmployeeId   = @EmployeeId WHERE EmployeeId   IN (SELECT EmployeeId FROM EmpIn)
UPDATE Orders SET CustomerId = @CustomerId WHERE CustomerId IN (SELECT CustomerId FROM CustIn)

SELECT GetDate() AS systime, 'Starting Set Processing' AS sysmessage
GO

SET NOCOUNT OFF
DROP TABLE EmpIn
DROP TABLE CustIn
GO