Help with SQL transactions

  • I have to make a stored procedure that deletes a record and inserts a new record. My teacher says to use Transactions instead of the UPDATE. Anyone have ANY examples of a transaction used in a stored procedure? I cant find anything on the web

  • Check out the Books Online under BEGIN TRAN or COMMIT TRAN. There is a lot of information there.

    Guarddata-

  • It would look something like this:

    Create proc TranExample

    as

    begin tran

    --removes destination rows that match source rows

    delete Destination

    From Source

    Where Destination.UniqueKey = Source.UniqueKey

    --If there was an error rollback the tran and quit(add other error handling if necessary)

    IF @@Error <> 0

    Begin

    rollback tran

    Return

    END

    --Inserts the Source data into Destination

    Insert Destination

    Select * From Source

    --If Error rollback, else commit

    IF @@Error <> 0

    Begin

    rollback tran

    END

    Else

    Begin

    commit tran

    END

    Signature is NULL

Viewing 3 posts - 1 through 2 (of 2 total)

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