Looping in sql sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Looping in sql sp

Author

Topic

DavidRhodes
Starting Member

40 Posts

Posted - 2004-06-23 : 10:35:51

Just trying to figure out the best way to do this

Scenario: Shopping Cart App
I have the following tables

CartItems (CartItemID, CartID, ItemID, Quantity, DateAdded)
CartItemChoices (CartItemID [FK], ChoiceID)

OrderItems (OrderItemID, OrderID, ItemID, Quantity)
OrderItemChoices (OrderItemID [FK], ChoiceID)

Basically, what I need to do is at the point the order is confirmed (paid for) move all records from CartItems to OrderItems and CartItemChoices to OrderItemChoices maintaining the relationship between the tables (Items and ItemChoices) and also deleting the records from the cart tables after they are transferred.

The only way I can think of doing it at the moment is looping the CartItems table one at a time, move the record to the OrderItems and loop the CartItemChoices table inside the other loop to move the choices. I can do this with cursors but is there any way to do it without

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-23 : 11:11:31

if you are doing this in .net with datasets it's easier to do it there. if you have a relationship in the dataset between dataTables it automaticaly makes the correct keys in parent-child tables.

in sql i gues u need a #temptable or a triger somewhere...
you could put it on delete in CartItems table. so when you delete the record you insert it in the OrderItems.
same goes for CartItemChoices.

Go with the flow & have fun! Else fight the flow :)

DavidRhodes
Starting Member

40 Posts

Posted - 2004-06-23 : 11:16:11

Yeah, it's .net but i'd rather do it all in one stored procedure

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-23 : 11:27:26

the correct keys are bit of a problem in one sp...
the first thing that comes to mind is:

1. insert CartItems into OrderItems
2. insert inserted OrderItems into #tempTable (you get the keys) <- do the CartItems inner join OrderItems on (relevant fields), #tempTable should have both CartItemID and OrderItemID
3. then insert CartItemChoices into OrderItemChoices using the OrderItemID from #tempTable. join field here is CartItemID

there probably is a simpler way, but at the moment i can't think of one.

Go with the flow & have fun! Else fight the flow :)

DavidRhodes
Starting Member

40 Posts

Posted - 2004-06-23 : 11:56:07

I managed to do it, here's what I did

CREATE PROCEDURE [spCartToOrder]

@UserID int,
@CartID nvarchar(50),
@AddressID int,
@TxAuthNo nvarchar(200),
@SecurityKey nvarchar(200),
@VPSTxID nvarchar(200)

AS

BEGIN TRANSACTION

	DECLARE
		@OrderID int,
		@CartItemID int,
		@OrderItemID int

	-- CREATE ORDER AND GET ORDERID
	INSERT INTO [Orders] ([UserID], [OrderDate], [AddressID], [TxAuthNo], [SecurityKey], [VPSTxID]) 
	VALUES (@UserID, GETDATE(), @AddressID, @TxAuthNo, @SecurityKey, @VPSTxID)
	SET @OrderID = @@IDENTITY

	-- GET FIRST CARTITEMID
	SELECT @CartItemID = MAX([CartItemID]) FROM [CartItems] WHERE [CartID] = @CartID

	-- START ITEM LOOP
	WHILE (@CartItemID IS NOT NULL)
	BEGIN
		INSERT INTO [OrderItems] ([OrderID], [ItemID], [Quantity]) SELECT @OrderID, [ItemID], [Quantity] FROM [CartItems] WHERE [CartItemID] = @CartItemID
		SET @OrderItemID = @@IDENTITY

		-- START CHOICE LOOP
		INSERT INTO [OrderItemChoices] ([OrderItemID], [ChoiceID]) SELECT @OrderItemID, [ChoiceID] FROM [CartItemChoices] WHERE [CartItemID] = @CartItemID
		-- END CHOICE LOOP

		-- DELETE ITEM AND CHOICES
		DELETE FROM [CartItemChoices] WHERE [CartItemID] = @CartItemID
		DELETE FROM [CartItems] WHERE [CartItemID] = @CartItemID

		-- GET NEXT CARTITEMID
		SELECT @CartItemID = MAX([CartItemID]) FROM [CartItems] WHERE [CartID] = @CartID
	END
	-- END ITEM LOOP

COMMIT TRANSACTION
GO

Can anyone see anything wrong with that?

DavidRhodes
Starting Member

40 Posts

Posted - 2004-06-23 : 17:04:54

Is this the best way to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 17:06:58

No. Looping in SQL Server is bad. If you posted DDL and DML, we'd be able to come up with a solution. DDL is CREATE TABLE statements for the tables. DML would be INSERT INTO statements for sample data to load into the tables, what it would look like before the query runs. And then we'd need to see what the expected outcome would be after the query runs using the sample data.

Tara

DavidRhodes
Starting Member

40 Posts

Posted - 2004-06-23 : 17:23:14

I'd have to give you DDL for about 10 tables if you wanted to try it and come up with a solution. I'll see if I have time at work tomorrow.
In the meantime, what is wrong with looping, in particular the above sp. Can you point me in the right direction of how it should be done

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-23 : 17:25:05

SQL Server is not designed to perform well with looping, whether it be a WHILE loop or a cursor. SQL performs best with set-based operations, which means no loops. Typically, you can avoid a loop if you use a JOIN. Some people don't realize that you can use JOINs in DELETE/INSERT/UPDATE statements.

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-24 : 08:02:21

looping is really not the best idea. but if you have less then 10 order items per purcahse it's ok to do it
this way if you don't have time to do it set-based. for more than 10 items and say 5 custumers at once, it can be a problem.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources