• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Rollback and insert error message into table

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
JRockFL
Asked:
JRockFL
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Rajesh_mjCommented:
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
 
Rajesh_mjCommented:
Sorry,
@AnyError variable is not required in my last comment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now