August 7, 2003 at 10:29 pm
CREATE PROC Usp_Ins....
INSERT INTO
.....
.........
IF @@ERROR <> 0
GOTO ERROR_HANDLER
RETURN
ERROR_HANDLER:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RAISEERROR(???????)
RETURN
In my Stored Procedure I am trapping error with @@ERROR and directs to error handler if error occurs. I need to raise error with the error message that occured in Insert like
'Cannot update identity column '%.*ls'.' with
arugment values for arguemnt list. How is it possible
August 8, 2003 at 11:53 am
Big topic, but to get you started...
Basically, the tools you will need are a few variables to store the values you want to return in you error handler, a lookup to sysmessages to get the text of the error message with the number returned, and any othe processing you want. Something like...
DECLARE
@ErrorNumber INT
, @RowsAffected INT
, @ErrorText NVARCHAR(510)
, @TableName VARCHAR(50)
-- Transaction stuff here...
SET @TableName = 'MyTable1'
-- Do insert, then trap @@error, since it
-- will ose its value after another
-- line is executed...
INSERT INTO MyTable1 VALUES (...)
SELECT
@ErrorNumber=@@ERROR
, @RowsAffected=@@ROWCOUNT
-- Now you have the variables, so go
-- to error handler...
IF (@ErrorNumber <> 0) BEGIN
GOTO ErrorHandler
END
-- ... Continue with processing ...
ErrorHandler:
-- Get the error message
SELECT @ErrorText=description
FROM master..sysmessages
WHERE error = @ErrorNumber
-- Manipulate the text at will
-- Perhaps replace %*.ls with table name, etc
-- ... do stuff
ROLLBACK TRANSACTION
-- Raiserror WITH LOG. Look in BOL for
-- more info on the WITH LOG option...
RAISERROR(@ErrorText, 16, 1) WITH LOG
RETURN
Hope this gets you started,
Jay
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply