Handling Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Handling Error

Author

Topic

M2
Starting Member

22 Posts

Posted - 2003-09-07 : 22:16:52

I want handling error in following insertion operation. Could any one pls help me check whether it's correct or not ? Because I cant test it under Query Analyzer.

DECLARE @RegionID int
DECLARE @Name varchar(20)
DECLARE @msg varchar (255)
DECLARE @errorNumber int

Set @RegionID = 'A1'
Set @Name = '723k'

BEGIN TRANSACTION

INSERT INTO Branch (RegionID,Name)
VALUES (@RegionID,@Name)

SET @errorNumber = @@error

IF @errorNumber <> 0

BEGIN
SET @msg = 'Error: ' + CAST(@errorNumber as varchar(10)) + 'occurred during insert branch record !'
RAISERROR 50000 @msg
Rollback Transaction
RETURN
END

COMMIT TRANSACTION

Select ID,RegionID,Name from Branch
Order by ID desc

Thanks ! M2

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-08 : 02:40:52

There is no need for an explicit transaction as this is a single update statement. Apart from that it is fine

DECLARE @RegionID int
DECLARE @Name varchar(20)
DECLARE @msg varchar (255)
DECLARE @errorNumber int

Set @RegionID = 'A1'
Set @Name = '723k'

INSERT INTO Branch (RegionID,Name)
VALUES (@RegionID,@Name)

SET @errorNumber = @@error

IF @errorNumber <> 0
BEGIN
SET @msg = 'Error: ' + CAST(@errorNumber as varchar(10)) + 'occurred during insert branch record !'
RAISERROR 50000 @msg
RETURN
END
Select ID,RegionID,Name from Branch
Order by ID desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dsdeming

479 Posts

Posted - 2003-09-08 : 08:27:53

quote:
I want handling error in following insertion operation. Could any one pls help me check whether it's correct or not ? Because I cant test it under Query Analyzer.

Why can't you test it in Query Analyzer? If either Branch.RegionID or Branch.Name is not nullable, passsing a NULL to either of them will generate an error you could trap.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources