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

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
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. http://msdn.microsoft.com/msdnmag/issues/06/05/DataPoints/
http://www.4guysfromrolla.com/webtech/041906-1.shtml
http://articles.techrepublic.com.com/5100-9592_11-6084469.html Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
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.

]]>