Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to use Transaction and Try Catch in Stored Procedure

    • 0
    • 2
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 144
    Comment on it

    How to use Transaction and Try Catch in Stored Procedure

    Suppose you are working on multiple table in a database eg updating, deleting and inserting on multiple table then it is always a good practice to maintain Transaction property as using this will undergo changes in all the tables or none. Here is a simple example to implement that.


    CREATE PROCEDURE TEST

    AS

    BEGIN
    BEGIN TRY
    BEGIN TRANSACTION

    ----------Write Your Queries here








    --------write this in the end of your procedure
    DECLARE @ERROR INT
    SET @ERROR = @@ERROR
    --------@@ERROR is a global variable which will return a non- zero when any error occurs in a procedure

    IF @ERROR <> 0
    BEGIN
    GOTO LOGERROR
    END


    GOTO PROCEDUREEND




    LOGERROR:
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)
    SELECT @ERRORMESSAGE = [description] FROM master.dbo.sysmessages
    where error = @error
    --------There is a table in master database which contains records of all errors

    INSERT INTO ERRORLOG(ERRORDATE,ERRORSOURCE,ERRORMESSAGE,@ERRORCODE)
    VALUES(GETDATE(),'PROCEDURE NAME',@ERRORMESSAGE,@ERROR)
    --------If you want to log errors then create a table in your database with the above structure




    PROCEDUREEND:

    COMMIT TRANSACTION
    --------This will complete the transaction

    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION
    --------This will rollback the transaction

    END CATCH

    END

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: