Cursor Loop Problem

Hey everyone,

I'm running a cursor to concatinate text into a field. I have a work order table with workOrderID as the unique identifyer. I have a comments table that has a foreign key to workOrderID. There is a one-to-many relationship between the tables as a work order can have multiple comments.

I've got a field in the work order table that is supposed to store all the comments for that work order concatenated together. This is the script I am using:

DECLARE @.Comments varchar(8000);

DECLARE @.WorkOrderID VARCHAR(9); -- unique identifier

DECLARE C1 CURSOR

FOR

SELECT c.Comment , w.JobOrder

FROM tblWorkOrderComments AS c

INNER JOIN tblCallIns AS w

ON c.WorkOrder = w.JobOrder

WHERE w.Comments IS NULL;

OPEN C1;

FETCH NEXT FROM C1 INTO @.Comments ,@.WorkOrderID ; -- include the unique identifier into Fetch

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN

IF (@.@.FETCH_STATUS <> -2)

BEGIN

UPDATE tblCallIns

SET tblCallIns.Comments = ISNULL(tblCallIns.Comments,'') + CHAR(13) + ISNULL(@.Comments,'') -- concatenate comments

WHERE JobOrder = @.WorkOrderID -- filter updated records

END;

FETCH NEXT FROM C1 INTO @.Comments ,@.WorkOrderID ;

END;

CLOSE C1;

DEALLOCATE C1;

It's only pulling the first comment into the comments field and not grabbing any additional comments for the work order.

Can anyone see what's wrong with my code?

Thanks,

Lee

After the UPDATE the w.comments referenced in the cursor is no longer null.

|||

Lee,

I think you want to specify FORWARD_ONLY, STATIC, and KEYSET in your CURSOR declaration.

Otherwise the CURSOR is DYNAMIC, and working in light of your UPDATE actions.

http://msdn2.microsoft.com/en-us/library/ms180169.aspx

Hope that helps,

Dan

|||hi try this..

DECLARE @.tblWorkOrderComments TABLE (WorkOrder INT, Comment VARCHAR(800))
DECLARE @.tblCallIns TABLE (JobOrder INT, Comments VARCHAR(800))

INSERT INTO @.tblWorkOrderComments VALUES (1, 'the')
INSERT INTO @.tblWorkOrderComments VALUES (1, 'quick')
INSERT INTO @.tblWorkOrderComments VALUES (1, 'brown')
INSERT INTO @.tblWorkOrderComments VALUES (2, 'fox')
INSERT INTO @.tblWorkOrderComments VALUES (2, 'jumps')
INSERT INTO @.tblCallIns VALUES (1, NULL)
INSERT INTO @.tblCallIns VALUES (2, NULL)
INSERT INTO @.tblCallIns VALUES (3, NULL)
INSERT INTO @.tblCallIns VALUES (4, NULL)

DECLARE @.Comments varchar(8000);
DECLARE @.WorkOrderID VARCHAR(9); -- unique identifier

DECLARE C1 CURSOR FOR
SELECT DISTINCT w.JobOrder -- get only the joborder with comments
FROM @.tblWorkOrderComments AS c
INNER JOIN @.tblCallIns AS w
ON c.WorkOrder = w.JobOrder
WHERE w.Comments IS NULL;

OPEN C1;

FETCH NEXT FROM C1 INTO @.WorkOrderID ; -- include the unique identifier into Fetch

WHILE (@.@.FETCH_STATUS = 0)

BEGIN

--IF (@.@.FETCH_STATUS <> -2)

--BEGIN

SET @.Comments = ''

SELECT @.Comments = @.Comments + CHAR(13) + ISNULL(Comment,'') -- this will concatenate all the comments per workorder
FROM @.tblWorkOrderComments
WHERE WorkOrder = @.WorkOrderID
ORDER BY
WorkOrder

UPDATE @.tblCallIns

SET Comments = @.Comments -- concatenate comments

WHERE JobOrder = @.WorkOrderID -- filter updated records

--END;

FETCH NEXT FROM C1 INTO @.WorkOrderID ;

END;

CLOSE C1;

DEALLOCATE C1;

SELECT * FROM @.tblWorkOrderComments
SELECT * FROM @.tblCallInssql