another stored procedure question - ms sql

[bbolte]

i have this ado transaction happening in an asp page with 2 sql statements, here's the code:

'set up to move items from cart table into order details table
'begin ado transaction
connNav.BeginTrans
'transfer cart items
strSQL3 = "INSERT INTO orderDetails (orderRefNum, partNo, quantity, description) " &_
			"SELECT '" & intOrdRefNum & "', partNo, quantity, 'none' FROM cart " &_
			"WHERE userID = " & userID
connNav.Execute(strSQL3)
'empty cart table of items
strSQL4 = "DELETE FROM cart WHERE userID = " & userID
connNav.Execute(strSQL4)
'end the transaction
connNav.CommitTrans

can this all be done in one stored procedure and still keep the transaction?

[JimBolla]

CREATE PROCEDURE dbo.sp_DoStuff  -- you can pick a better name :)
(
	@userid int
        @intordrefnum int
)
AS
BEGIN TRAN

INSERT INTO orderDetails (orderRefNum, partNo, quantity, description) SELECT @intorrefnum, partNo, quantity, 'none' FROM cart WHERE userID = @userid

DELETE FROM cart WHERE userID = @userid

COMMIT TRAN

[bbolte]

wow, that's great! thanks. this stuff is blowing my mind and i can't seem to find any good stuff on the web about it. i must get a book or something. crowdozer, i was talking to someone last night about this and he told me that doing a lot of deletes like this would fill up the log files and may bring the server down. he said to use something called "truncate" instead. any thoughts?

[JimBolla]

uh. i think your friend is misinformed. the way SQL Server works is every command you perform that modifies the database is logged to the transaction log file. this is for the purpose of backing up and rolling back transactions and what have you. so yes, every delete command you issue does increase the size of the transaction log. but unless your database server is severely limited in harddrive space, this should never really be a problem.

truncating: truncating a table wipes all data out of the table. it does not take a where clause or anything like that, therefore would really be useful in your scenario. also i believe there are some transactional issues with truncating a table if the need arises to rollback, although i'm not 100% sure about this.

[bbolte]

oh, ok. i don't think i would want to use it here as i'm only needing to delete the record(s) that corrosponds to the userID (there may be several different userIDs at the time). one other thing, will this delete all the records that equal that id? there may be several rows that need to come out.

[JimBolla]

it will delete any number of records that delete the criteria specified in the WHERE cluase.

[bbolte]

ok, thanks much!

[MattR]

TRUNCATE TABLE is much faster than 'DELETE table' for clearing out the whole table since it is non-logged (or minimally logged).

Plus you don't have to worry about the logsegment becoming full and all your transacts backing up.