May 8, 2008 at 6:12 am
Hello All
After an INSERT stmt how do I engage error handleling and capture errors to a table
create table error_log ....... stmt here
declare @ERRNUM INT
declare @errmessage varchar(200)
Example
INSERT into States('OH', 'OHIO','MIDWEST')
SELECT @ERRNUM = @@ERR
IF @ERRNUM > 0
CHECK FOR A DUPLICATE OHIO RECORD --- PK OH alerady exists
if duplicated PK capture error and some time of message in an error_log table
CHECK TO SEE MIDWEST IS A VALID REGION IN the region table
if 'MIDWEST' can not be found in th REGION table
capture the value 'MIDWEST' and store with err message in an error_log table
May 8, 2008 at 10:04 am
Try this,
[font="Courier New"]DECLARE @ID AS CHAR(2)
DECLARE @Name VARCHAR(10)
DECLARE @Region VARCHAR(10)
SET @ID = 'OH'
SET @Name = 'OHIO'
SET @Region = 'MIDWEST'
IF EXISTS(SELECT * FROM States WHERE [ID]=@ID)
BEGIN
INSERT INTO error_log VALUES(@ID + ' already exists')
END
ELSE
BEGIN
IF EXISTS (SELECT * FROM Region WHERE RegionID=@Region)
BEGIN
INSERT INTO error_log VALUES(@Region + ' does not exists in the region table')
END
ELSE
BEGIN
INSERT into States(@ID, @Name,@Region)
END
END[/font]
- Zahran -
May 8, 2008 at 11:17 am
THANKS
I will give it a try
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply