How to Handle Exception and Error in SQL Server 2000 and SQL Server 2005 (Transactions – Rollback Transaction, Commit Transaction)

SQL Server 2000 doesn’t have a standard and structured way to handle exception in SQL Query. However, we can user the standard error variable @@ERROR to check whether any error has occurred in order to take the necessary action.

Example for SQL Server 2000 to handle exception:-

BEGIN TRANSACTION
DECLARE @Err AS INT
-- Put your statements here.
SET @Err = @@ERROR
IF @Err <> 0
  BEGIN
    ROLLBACK TRANSACTION
    SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
  END
ELSE
  BEGIN
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
  END

However, SQL Server 2005 has come with a structured way to handle the exception (Try-Catch blocks). This structure is very similar to those used in C#, VB.Net and Java.

Example for SQL Server 2005 to handle exception :-

BEGIN TRY
  BEGIN TRANSACTION
  -- Put your statements here.
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
END CATCH

Query a field size in Database (SQL Server)

If you want to query the size in bytes for a record or field in database (SQL Server), you can use the DATALENGTH function.
The DATALENGTH function can return the length in bytes for any data types including text, ntext, image and varbinary.

For example you want to know the size of Field1 in TableA just run the following query:

SELECT Field1, DATALENGTH(Field1) AS [Size Field1 in bytes] FROM TableA

The output will be the value of Field1 and it's size in bytes for each rows record in TableA