T-SQL New features in SQL Server 2005 - Part 1

I have been working in SQL Server since past 3 years or so. I like solving complex problems and deriving the simplest, yet efficient, query. Though, I was always able to solve the problem at hand using the constructs provided in SQL Server 2000, but there were times when I felt myself restricted.. or wasn't particularly happy the way I solved a problem. With new constructs and clauses, now available, in SQL Server 2005 T-SQL, I feel that I am able to write the query in a neat, manageable way. This can be compared with the Generics introduced in .Net 2.0 in a way that it does not enable you to do something that wasn't at all possible earlier, but just that it provides a easy to maintain and efficient ways of doing the same thing. In coming days, we will be discussing the following features:

1. Error Handling
2. Top clause
3. Common Table Expression
4. Recursive Queries
5. Output clause
6. Intersect and Except
7. Apply
8. Ranking Function

Error Handling:
In prior version of SQL Server, the way to check if there was an error in a SQL statement was to use @@Error which should be used immediately after the SQL statement. It was mandatory to follow this practice since the value in @@Error was reset after every statement.
With the new structured error handing mechanism provided in T-SQL 2005, it provides a straighter forward, simpler and better way to handle exceptions in code. This can be understood more clearly by the following example:

Writing the following query will result in “Divide by zero” error:

SELECT 1/0

If the statement which is expected to throw an exception is enclosed in a try-catch block, the same situation can be handled in an elegant manner:

BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

PRINT 'ErrorNumber= ' + CONVERT(VARCHAR(10), Error_Number())

PRINT 'ErrorMessage= ' + Error_Message()

END CATCH

This will now handle the exception and display the Error number along with the message.

Apart from the above mentioned functions for obtaining the exception details, SQL Server also provides the following:

ERROR_NUMBER() - Number of error
ERROR_SEVERITY() - Severity of error
ERROR_STATE() - State of error
ERROR_MESSAGE() - Error Message
ERROR_LINE() - Line on which error occurred
ERROR_PROCEDURE() - Procedure in which error occurred
XACT_STATE() - Transaction state

The example shown here just gives an idea of what can be achieved using structured exception handing in SQL Server. I also feel, this approach should be a welcome feature by any .Net programmer since the construct is almost similar to that a .Net developer is familiar with.