How to use transactions in stored procedures

• spinto

  SSC Rookie

  Points: 31

  More actions

  May 9, 2002 at 11:03 am

  #78147

  I want to write all my insert, update and delete stmts in multiple transactions and want to rollback if something fails.

  How do I do this.

  eg.begin transaction

  /*Insert new clients , generate client id's*/

  Insert Into tblClient(ClientNm, Address, City, State, Zip, Phone, Fax, WebAddr, HotNote)

  Select Company, Address, City, State, Zip, substring(Phone,1,30), substring(Fax,1,30), URL, HotNote from #tblTemp

  /* Log this information with a time stamp to a log file*/

  Insert Into tblClientlog(ClientNm, Address, City, State, Zip, Phone, Fax, WebAddr, HotNote)

  Select Company, Address, City, State, Zip, substring(Phone,1,30), substring(Fax,1,30), URL, HotNote from #tblTemp

  DROP Table #tblTemp

  update tblClientLog set clientid = b.clientid

  from tblclientlog a, tblclient b

  where a.company = b.clientnm

  end

• Eve

  SSCrazy

  Points: 2480

  More actions

  May 9, 2002 at 11:15 am

  #428818

  You have to use an error check and rollback your trans if @@Error<>0.

  Check the script http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=275 that shows how to do it.