Issue in Transaction

Dear All,

I'm very much new to SQL and right now working with basics before moving into complex SPs.

I'm facing issues in executing a simple transaction in sql. The following is the code:

"

BEGINTRANSACTION

INSERT INTO MyDatabase.dbo.Order (OrderID) values('1234')

INSERTINTO MyDatabase.dbo.MyTable (Age)values('we')

IF(@.@.ERROR<> 0)

BEGIN

PRINT('Exception Raised')

ROLLBACKTRANSACTION

END

COMMITTRANSACTION

"

The age column is of int data type and I'm trying to rollback transaction by passing char value to that column. On executing the above query, it does not go to rollback transaction part whereas it gives the error

"

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value 'we' to data type int.

"

Kindly request you all to help me in this regard.

Most conversion errors will fire a batch abort error, which will stop the batch from executing. If you are using SQL Server 2005, you can use the new error functionality which allows you to catch statement as well as batch abort errors.

Jens K. Suessmeyer.

http://www.sqlserver2005.de