November 7, 2003 at 4:02 am
Hi Folks,
I know the more experienced of you have gone through this a thousand times, but I still can't really get my head around it.
OK, I absolutely agree that if data can be retrieved with a set based approach it is best. But in some cases that is not possible and so I see no other alternative but to use a cursor. In the example below, what other alternative do I have for the two separate cases?
DECLARE @ComponentID int
DECLARE @OrderID int
DECLARE @NewStateID int
DECLARE @ExpComp varchar(10)
DECLARE Example CURSOR FOR
SELECT ComponentID, OrderID, NewStateID FROM Component
WHERE StateChanged = 1
OPEN Example
FETCH NEXT FROM Example INTO @ComponentID, @OrderID, @NewStateID
WHILE (@@FETCH_STATUS=0)
BEGIN
--FIRST CASE: Call SP iteratively for each value of @ComponentID
EXEC @rv = SaveState @ComponentID, @newStateID
--SECOND CASE: Carry out additional business logic and update a second table
IF @OrderID BETWEEN 1 AND 100
SET @ExpComp = 'First 100 orders"
ELSE
SET @ExpComp = 'All other orders"
UPDATE ComponentOrders SET OrderDescription = @ExpComp WHERE ComponentID = @componentID
FETCH NEXT FROM Example INTO @ComponentID, @OrderID, @NewStateID
END
Thanks
Mauro
November 7, 2003 at 4:30 am
For the second case, it is rather simple.
Just do a joining update ...
UPDATE ComponentOrders
SET OrderDescription = CASE WHEN Component.OrderId <= 100 THEN 'First 100 orders' ELSE 'All other orders'END
FROM Component C
INNER JOIN
ComponentOrders CO
ON CO.ComponentID = C.componentID
November 7, 2003 at 5:30 am
Second Case - Ahaha, finally I see a neat example of how to use CASE. Always meant to look into it but never did. Thanks for that.
First Case - The SP was entirely made up, but the concept is the following
a) I have an SP (SP1) which can be called by other SPs or by VB, ASP code, etc
b) In some SPs I need to first retrieve a set of data, and then call SP1 to do some other stuff.
c) Sure, if SP1 is a simple update, then I would be back in the Second Case scenario above. But for more complicated SPs, I really want to implement a modular approach and keep SP1 as standalone.
Does that clarify it better?
Mauro
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply