begin tran in Store Procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

begin tran in Store Procedure

hi, just get some idea what is the best way to put in begin tran in a store procedure and if got any
error during transaction, need to capture error message as output parameter. alter procedure dbo.PostTransaction(@vcDocumentNo varchar(20), @vcErrorMessage varchar(max)) begin tran insert into …
Select …. Set @vcErrorMessage = @@Error …
if @vcErrorMessage <> ” then goto A
insert into …
Select …. Set @vcErrorMessage = @@Error …
if @vcErrorMessage <> ” then goto A
update …. insert into …
Select …. …..
Commit tran A : if @vcErrorMessage <> ” roolback what is the Transact-SQL for above method, please help !!!
Thanks in Advance
Madhivanan Failing to plan is Planning to fail
from above example, if there is any error occur during transaction, the SQL server will display the error message in Query ->messages, how can i capture this messages and keep it in a variable ? because i need to reutrn this message back to program.
SQL Server 2005, @@ERROR variable is no longer needed after every statement executed, as was the case in SQL Server 2000. SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.