October 14, 2003 at 2:52 pm
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
October 14, 2003 at 3:00 pm
Check out the Books Online under BEGIN TRAN or COMMIT TRAN. There is a lot of information there.
Guarddata-
October 15, 2003 at 6:21 pm
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