TOP 1 WITH TIES

Its time to to forget the @@Error Global variable. SQL Server 2005 Provides you the power of structured Error handling as you do in any .Net Language, Which eliminates the need for checking IF @@ERROR > 0 ...

Now lets just create a table

CREATE TABLE #Student(StudentID int PRIMARY KEY, StudentName VARCHAR(50))

And try the following.

BEGIN TRY
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
END TRY
BEGIN CATCH
PRINT 'Insert Failed'
END CATCH

When you run the above code for the first time you will get the message 'Successfully Inserted'. When you try the code for the second time, You will get the message saying 'Insert failed', which means that our Catch block executed.

You can see that, when a statement within the TRY block fails, control is passed to the first statement within the catch block.

In the catch block you can use the new ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY() and ERROR_STATE() functions to get the details of the error. For eg. you can modify the above code as follows to get the details of the Error.

BEGIN TRY
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
END TRY
BEGIN CATCH
PRINT 'Insert Failed'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH

TRANSACTIONS and Error Handling
The new XACT_State function will come handy when you are using explicit transactions in the Try block. The XACT_STATE() function returns the transaction state. The possible values are -1,0 and 1.

-1 : Means that a transaction is open, but in a doomed (uncommitable) state. This typically happens when an error occutred, which has a severity level higher than 17.
0 : No transactions are open
1 : a transaction is open and commitable.

The following code shows the usage of XACT_STATE() function.

BEGIN TRY
BEGIN TRAN
INSERT INTO #Student(StudentId, StudentName) VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT XACT_STATE()
ROLLBACK TRAN
PRINT XACT_STATE()
PRINT 'Insert Failed'
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
END CATCH

Note that the error will not be propogated to the application from the try block, unless you explicitly throw the error using RAISERROR, as in the following code segment.


BEGIN TRY
BEGIN TRAN
INSERT INTO #Student(StudentId, StudentName)
VALUES(1, 'Roji. P. Thomas')
PRINT 'Successfully Inserted'
COMMIT TRAN
END TRY
BEGIN CATCH
RAISERROR('Insert Failed',16,1)
END CATCH

For more information see the following MSDN article.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp