Usage of Try Catch in SQL SERVER 2005 for Rollback a Transaction

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

BEGIN TRY Try Statement 1 Try Statement 2 ... Try Statement M END TRY BEGIN CATCH Catch Statement 1 Catch Statement 2 ... Catch Statement N END CATCH

The following system functions are available in the CATCH block and can be used to determine additional error information:

Function	Description
ERROR_NUMBER()	Returns the number of the error
ERROR_SEVERITY()	Returns the severity
ERROR_STATE()	Returns the error state number
ERROR_PROCEDURE()	Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE()	Returns the line number inside the routine that caused the error
ERROR_MESSAGE()	Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

Also realize that not all errors generating by the TRY block statements are passed onto the CATCH block. Any errors with a severity of 10 or less are considered to be warnings and do not branch control flow to the CATCH block. Also, any errors that sever the database connection will not cause the CATCH block to be reached.

Let's look at a quick example of using TRY...CATCH, after which we'll turn our attention to using this new construct for rolling back transactions in the face of an error. The following example shows a very simply INSERT query on the Northwind database's Products table. The Products table's ProductID column is an IDENTITY column and therefore its value can't be specified when inserting a new record. However, I've specified this value in the following INSERT statement. Hence, control is turned over to the CATCH block where error information is displayed.

BEGIN TRY -- This will generate an error, as ProductID is an IDENTITY column -- Ergo, we can't specify a value for this column... INSERT INTO Products(ProductID, ProductName) VALUES(1, 'Test') END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR_MESSAGE() END CATCH

This query will return a single record with a single column with the contents: "There was an error! Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF."

Using TRY...CATCH to Rollback a Transaction in the Face of an Error
As discussed earlier in this article, one of the downsides of the @@ERROR variable approach is that for transactions a check against this variable must be added after each and every SQL statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly simplified, as the following example illustrates:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS BEGIN TRY BEGIN TRANSACTION -- Start the transaction -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID -- If we reach here, success! COMMIT END TRY BEGIN CATCH -- Whoops, there was an error IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH

In the TRY block a transaction is started and the two DELETE statements are performed. If both DELETEs succeed, the COMMIT will be reached and the transaction committed. If, however, either one produces an error, control will be routed to the CATCH block where the transaction will be rolled back. Also, the CATCH block re-raises the error (using RAISERROR) so that the error information will be percolated up to the application that invoked the stored procedure. For an ASP.NET web application, that means that an exception will be raised in the .NET code that invoked this stored procedure, as chances are you not only want to rollback the transaction, but have some sort of error message handled in the web application as well so that the end user knows that their action failed.

Adding the call to RAISERROR in the CATCH block is tantamount to having a TRY...CATCH block in a programming language that re-throws the exception in the CATCH block after logging it or performing other actions. The action performed in the example above is rolling back the transaction, but could also include logging logic. If you omit the RAISERROR, the ASP.NET application won't throw an exception from executing the database command. Chances are you want to have an exception thrown on the ASP.NET side (so that you don't fail silently). If so, leave in theRAISERROR call.

Conclusion
SQL Server 2005's new TRY...CATCH block brings the familiar TRY...CATCH exception handling to T-SQL. Prior to SQL Server 2005, errors could only be detected in SQL scripts through the use of the @@ERROR variable, which annoyingly reset after each SQL statement, thereby requiring checks after each and every statement. This led to bloated script that was prone to typos or cut and paste errors leading to potentially serious problems. With SQL Server 2005's TRY...CATCH block, on the other hand, anytime an error is raised by one of the statements in the TRY block, control is directed to the CATCH block. As we saw in this article, the TRY...CATCH block allows for much more readable and cleaner error handling in transaction settings.

Happy Programming!

About @@ERROR System Variable for Rollback

Checking @@ERROR - the Old Way of Handling Errors in T-SQL

SQL Server provides an @@ERROR variable that indicates the status of the last completed SQL statement in a given batch. If a SQL statement is completed successfully, @@ERROR is assigned 0. If, however, an error occurs, @@ERROR is set to the number of the error message.

To see how the @@ERROR variable can be used, imagine that we have a data-driven web application that maintains employee information. Let's assume that our database has Employeesand EmployeePhoneNumbers tables, among others. These two tables share a one-to-many relationship; that is, each Employees record can have an arbitrary number of related records in theEmployeePhoneNumbers table. There might be one for their office phone, one for their pager, one for their cell phone, and so on. Imagine that our database includes a stored procedure,DeleteEmployee, which is comprised of two DELETE statements - one to delete the employee's related phone numbers from the system and one to delete the actual employee record:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID

Since we want these two delete statements to be atomic and either both fail or both succeed, we need to wrap up these two statements into a transaction. By using a transaction, we can rollback the transaction in the face of an error and undo any changes made since the start of the exception. To accomplish this we might initially try to use the following syntax:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS BEGIN TRANSACTION -- Start the transaction -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID -- See if there is an error IF @@ERROR <> 0 -- There's an error b/c @ERROR is not 0, rollback ROLLBACK ELSE COMMIT -- Success! Commit the transaction

This stored procedure (it appears) starts a transaction, runs the two DELETE statements, and then checks to see if there was an error. If there was one, it rolls the transaction back, else it commits the transaction. I say "it appears" because this syntax, while legal, is semantically incorrect because the @@ERROR variable is set after every SQL statement. Therefore, if the firstDELETE statement has an error the @@ERROR variable will be set to its error number. Then, the second DELETE will execute. If this second DELETE succeeds, @@ERROR will be set back to 0, in which case the transaction will be committed even though there was a problem with the first statement! Whoops!

Instead, a check must be made after every SQL statement to see if there has been an error. If so, the transaction must be rolled back and the stored procedure exited. This can lead to bulky scripts as a stored procedure with, say, five statements will have five checks against the @@ERROR variable. And if you forget to cut and paste a check in for a particular statement you're opening yourself up to a potential problem.

For more information on transactions and the @@ERROR syntax used for checking for errors and rolling back as needed, see Managing Transactions in SQL Server Stored Procedures.

css code for capital letters

text-transform : uppercase;

.formtitle {text-transform:uppercase; font-size:13px; }

Ajax not working in MVC3

in web.config

 <add key="UnobtrusiveJavaScriptEnabled" value="true" />

If you change the value from “true” to “false”, your Ajax will start working.  

Grid Lines for Griedview in ASP.Net C#

protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        for (int i = 0; i < e.Row.Cells.Count; i++)
        {
            e.Row.Cells[i].BorderWidth = 1;
            e.Row.Cells[i].BorderColor = System.Drawing.Color.Black;
        }


    }