Transaction is a logical unit of work that should be entirely completed or abort, no intermediate state is allowed. For example, if you sell a product to a customer, your transaction consists of at least two parts: you must update the inventory by subtracting the number of unites sold , and you must update the accounts receivable table in order to bill the customer. You want either updating both tables or neither. Updating only one is not acceptable.

In SQL you use "Begin Transaction" to mark the beginning of a transaction. Normally, if there is error--fail one activity(for example updating), you don't want to execute the others in the same transaction, you use "ROLLBACK TRANSACTION". Check error after each and every activity, if fail then rollback. use "COMMIT TRANSACTION" to commit changes only when all activities succeed.

@@ERROR <>0 only checks the latest sql statement. So better use it after each statement.

if you want to execute more than one statement after "IF" or "ELSE", put them between "BEGIN" and "END".

Use "EXEC procudureName" you can execute other procedures within procedures.

Here is an example:




SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[StoreprocedureName1]
(
    @Col1 varchar(255),
    @Col2 varchar(100),
    @EmailID CHAR(1)='0'
)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

INSERT TableName (ColumnName1,ColumnName2)
VALUES (@Col1,@Col2)

    IF @@ERROR <>0
    BEGIN
        ROLLBACK TRANSACTION
        RAISERROR('Insertion failed.',16,1)
        RETURN -1
    END

    ELSE
    BEGIN
        IF @EmailID = '1'
        BEGIN
            EXEC StoreprocedureName

            IF @@ERROR <>0
            BEGIN
                ROLLBACK TRANSACTION
                RAISERROR('StoreprocedureName execution failed.',16,1)
                RETURN -1
            END
        END
    END
COMMIT TRANSACTION

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO