SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    another stored procedure question - ms sql

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

    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?

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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

  3. #3
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it will delete any number of records that delete the criteria specified in the WHERE cluase.

  7. #7
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, thanks much!

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •