Solved

Rollback and insert error message into table

Posted on 2007-11-14
4
206 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 20280404
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
ID: 20280549
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
ID: 20287989
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
ID: 20287995
Sorry,
@AnyError variable is not required in my last comment
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

756 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