Solved

Rollback and insert error message into table

Posted on 2007-11-14
4
203 Views
Last Modified: 2008-02-01
I have a stored procedure that inserts does some inserts, updates and deletes.
If there is an error, I would like to record the error to the messagelog table
and rollback the transaction.

How can I do this?


BEGIN TRANSACTION

--Insert some data

--Insert some more data

--Delete some data

--update some data


If @@Error = 0
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC)
  (@MESSAGE, @SPROC)
0
Comment
Question by:JRockFL
  • 2
4 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
to start with, you will need to use the BEGIN END as you have more than 1 statement.

If @@Error = 0
  COMMIT TRANSACTION
ELSE
BEGIN
  ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC)
  (@MESSAGE, @SPROC)
END


now, the error message itself will be tricky...
but, check out this cool function:
http://www.nigelrivett.net/SQLTsql/spFormatOutputBuffer.html
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 150 total points
Comment Utility
BEGIN TRANSACTION

--Insert some data
IF  @@ERROR<>0 -- OR @@ROWCOUNT = 0
BEGIN
  ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC)
  (@MESSAGE, @SPROC)
 
  RETURN  
END

--Insert some more data
BEGIN
  ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC)
  (@MESSAGE, @SPROC)
 
  RETURN  
END
--Delete some data

BEGIN
  ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC)
  (@MESSAGE, @SPROC)
 
  RETURN  
END
--update some data

BEGIN
  ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC)
  (@MESSAGE, @SPROC)
 
  RETURN  
END

  COMMIT TRANSACTION
0
 
LVL 6

Accepted Solution

by:
Rajesh_mj earned 200 total points
Comment Utility
Hi,

Declare @AnyError Int,@MESSAGE varchar(100), @SPROC varchar(100)
SET @AnyError = 0

BEGIN TRANSACTION

--Insert some data
If @@ERROR <> 0
GOTO Err

--Insert some more data
If @@ERROR <> 0
GOTO Err

--Delete some data
If @@ERROR <> 0
GOTO Err

--update some data
If @@ERROR <> 0
GOTO Err

  COMMIT TRANSACTION
  RETURN       
      
Err:
 ROLLBACK TRANSACTION
  --would like to insert error into message log
  INSERT INTO MessageLog
  (Message, SPROC) values
  (@MESSAGE, @SPROC)
0
 
LVL 6

Expert Comment

by:Rajesh_mj
Comment Utility
Sorry,
@AnyError variable is not required in my last comment
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now