Transaction in SQL Server Consider a scenario, when 2 Stored Procedure called inside a Stored procedure like follows.
Note:  All 3 SP’s are implemented with TRANSACTION. 

CREATE PROC [dbo].[SP3]
AS
BEGIN
      BEGIN TRAN
EXEC SP1
EXEC SP2

      COMMIT TRAN
      END

GO


What happens when an error happened in SP2.

a.  Commits SP1 alone and raise error for SP2

b.  Rollback SP1 and SP2

c.  Syntax Error

d.  Commits SP1 and SP2 with Error Message

Please give the answer.

Thanks.
I assume you are working on SQL Server, given question tags and T-SQL notation. 

Nesting transactions in stored procedures as you are doing can turn into a mess. Basically, when you start a nested transaction, SQL Server does nothing but increment the transaction count and doesn't really start a "new" transaction. Every time you commit a transaction, you decrease the transaction count.

Usually I try to avoid using nested transactions and instead I check for existing transactions to "reuse".  Here you can find the stored procedure code template I use to handle nested procedure calls without nesting transactions.

Other than that, you have to handle commits and rollbacks with savepoints to obtain real transaction isolation from the "parent" transaction. If you don't do that, you get the default behavior, which is commit for the innermost transaction and rollback for the whole transaction stack.

Also, take into account that transactions need proper error handling and you have to make sure you get a rollback whenever you encounter an error. TRY/CATCH blocks can handle that for you, as I'm showing in the template I linked. Another thing worth noting is that even SET options can affect the code flow, so make sure you have XACT_ABORT set accordingly.