July 16, 2009 at 7:02 am
I was wondering if someone could take a look at the code below and offer any suggestions on how to accomplish what I want to accomplish. Change this so it works.
Basically, I need to insert the same number of records into a table called TicketTable for the number of tickets a customer has ordered. I have a cursor that goes through and finds the appropriate records to run this against and each one has a variable called @QUANTITY_ORDERED. I need to match the number of inserts into the TicketTable for the value in the @QUANTITY_ORDERED variable and then move on to the next record. I tried but it's not working. Not sure if I'm missing any extra BEGIN or END statements or it's just plain wrong.
Thanks!
Rog
OPEN TicketUpdate
FETCH FIRST FROM TicketUpdate INTO @BT_ID,@ST_ID,@PRODUCT_CODE,
@PRODUCT_MAJOR,@PRODUCT_MINOR,@QUANTITY_ORDERED,@DATE_ENTERED,@DATE_UPDATED,@ORDER_NUMBER,@ORDER_DATE,@SOURCE_CODE,@LAST_TICKET_NUMBER
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @ticket_count Integer
set @ticket_count = 0
SET @ticket_count = @ticket_count + 1
While (@ticket_count < @quantity_ordered)
BEGIN TRANSACTION
insert into TicketTable (SEQN,ID,ACTIVITY_TYPE,DESCRIPTION,PRODUCT_CODE,TRANSACTION_DATE,SOURCE_CODE,F_1,F_4,F_5)
values (@NEXT_SEQN,@BT_ID,'TICKET','Ticket',@PRODUCT_MAJOR,@ORDER_DATE,@SOURCE_CODE,@PRODUCT_CODE,1,@ORDER_NUMBER)
COMMIT TRANSACTION
FETCH NEXT FROM TicketUpdate INTO @BT_ID,@ST_ID,@PRODUCT_CODE,
@PRODUCT_MAJOR,@PRODUCT_MINOR,@QUANTITY_ORDERED,@DATE_ENTERED,@DATE_UPDATED,@ORDER_NUMBER,@ORDER_DATE,@SOURCE_CODE,@LAST_TICKET_NUMBER
END
CLOSE TicketUpdate
DEALLOCATE TicketUpdate
July 16, 2009 at 10:37 am
Try this
DECLARE @ticket_count Integer
OPEN TicketUpdate
FETCH FIRST FROM TicketUpdate INTO @BT_ID,@ST_ID,@PRODUCT_CODE,
@PRODUCT_MAJOR,@PRODUCT_MINOR,@QUANTITY_ORDERED,@DATE_ENTERED,@DATE_UPDATED,@ORDER_NUMBER,@ORDER_DATE,@SOURCE_CODE,@LAST_TICKET_NUMBER
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @ticket_count = 1
While (@ticket_count <= @quantity_ordered)
BEGIN
BEGIN TRANSACTION
insert into TicketTable (SEQN,ID,ACTIVITY_TYPE,DESCRIPTION,PRODUCT_CODE,TRANSACTION_DATE,SOURCE_CODE,F_1,F_4,F_5)
values (@NEXT_SEQN,@BT_ID,'TICKET','Ticket',@PRODUCT_MAJOR,@ORDER_DATE,@SOURCE_CODE,@PRODUCT_CODE,1,@ORDER_NUMBER)
COMMIT TRANSACTION
SET @ticket_count = @ticket_count + 1
END
FETCH NEXT FROM TicketUpdate INTO @BT_ID,@ST_ID,@PRODUCT_CODE,
@PRODUCT_MAJOR,@PRODUCT_MINOR,@QUANTITY_ORDERED,@DATE_ENTERED,@DATE_UPDATED,@ORDER_NUMBER,@ORDER_DATE,@SOURCE_CODE,@LAST_TICKET_NUMBER
END
CLOSE TicketUpdate
DEALLOCATE TicketUpdate
July 16, 2009 at 10:42 am
If you'd like help improving the performance of this update, post your table DDL and the Cursor SELECT. This looks like it can be done in a single update statement as opposed to a cursor. A set based update statement would beat the pants off the cursor in terms of performance.
July 16, 2009 at 2:36 pm
It worked out just fine and I really want to thank you for your help!
Rog
July 17, 2009 at 5:02 am
Roger Abram (7/16/2009)
It worked out just fine and I really want to thank you for your help!Rog
That's cool, but there're two ways to do things... the way that works and the way that works well. There's no need for a cursor here, if you're interested, but we do need to see the SELECT from the cursor declaration in order to show you how. 😉
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply