How to Use Transaction in SQL Server?

SQL Transaction gives you the “power to return back to a safe state if some error happens in the middle of your SQL Code”.

For example, suppose in your Stored Procedure you are running an Insert statement followed by Update statement. It is necessary that both these statements should complete successfully otherwise you should not run any of them.

You can do such things using SQL Transaction. Let me explain you how to do it.

SQL Transaction Example

Let me show you using a simple Stored Procedure. My stored procedure will delete a student whose id is passed to the SP.

I used the TRY and CATCH statement. In the TRY Block, I start with BEGIN TRANSACTION and then do the delete. At the end I used the COMMIT to save this to the database only if all codes work correctly without any error.

Note — Calling SQL Procedures directly from AJAX methods like — jQuery Load method or .post() method or .ajax() method, etc, is not possible.

For this you can implement an API in your jQuery code, this API calls a server side function which in turn executes the SQL Procedure.

You can check the tutorial — Consuming API in jQuery to learn how to do this.

If I get any error in the TRY block then control moves to the CATCH block. In the CATCH block I am doing the ROLLBACK, which means make all the statements after Begin Transaction (here the delete one) becomes void and returns back to the previous state.

The stored procedure is given below:

ALTER Procedure [dbo].[DeleteStudentTransaction]
@Id       INT
AS
BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM Student WHERE Id=@Id
        -- some other codes
        COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH

So if I pass student id 10 to the stored procedure and execute it, the student with id 10 will get deleted (provided there is no error).

Raising an Error for Testing Purpose

Now add the line RAISERROR(‘Some Random Error’,16,1) just after the delete statement and execute the stored procedure by passing student id 10.

ALTER Procedure [dbo].[DeleteStudentTransaction]
@Id       INT
AS
BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM Student WHERE Id=@Id
        RAISERROR('Some Random Error',16,1)
        COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH

Here the student id 10 will not be deleted because I am raising an error just after the delete statement. Thus the control will move to catch block where the whole thing will get rolled back.

Conclusion

That all — did you find it useful? I think SQL Transactions are powerful enough to build powerful Stored Procedures. Always use it to enhance your SQL Codes.