SQL While Loop

Question

Post reply

SQL While Loop

Roger Abram

Hall of Fame

Points: 3683
More actions
July 16, 2009 at 7:02 am

#206994

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

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

John Rowan SSC Guru Points: 56440 More actions · Answer 2

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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Roger Abram Hall of Fame Points: 3683 More actions · Answer 3

It worked out just fine and I really want to thank you for your help!

Rog

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)