title: Multiple Roll back for single Transcation
body: <p>I have to update multiple number of rows in table.
  My requirement is, If for any reason , the update result returns 0, then the entire transaction should be rolled back.
  At the same time if there is any exception occurred, then also the complete transaction must be rolled back.</p>
  
  <p>In short I need to roll back the entire update transaction either if update statement returns 0 or if any exception has been occurred while updating the table.</p>
  
  <p>This is the code I used.</p>
  
  <pre><code>CREATE TYPE [DBO].[EMPLOYEETYPETABLETYPE] AS TABLE
  ( EmployeeStatusKey INT, EmployeeStatusName VARCHAR(50) )
  
  CREATE PROCEDURE [dbo].[usp_UpdateEmployeeStatusType]
  
  @EmploymentStatusDetails [DBO].[EMPLOYEETYPETABLETYPE] READONLY 
  
  AS
   BEGIN
  
  SET NOCOUNT ON;
  
  DECLARE @TransactionName varchar(20) = 'UpdateEmployeeStatus';
  
  DECLARE @rowcount1 INT
  
      BEGIN
  
      BEGIN TRY
          BEGIN TRANSACTION @TransactionName
  
              UPDATE  ES1
              SET
                  ES1.EmployeeStatusName=ES2.EmployeeStatusName
  
              FROM 
                   [dbo].[EmployeeStatusTypes] ES1
              INNER JOIN 
                  @EmploymentStatusDetails ES2
              ON 
                  ES1.EmployeeStatusKey= ES2.EmployeeStatusKey
  
              SET
                  @ROWCOUNT1=@@ROWCOUNT
  
              IF @rowcount1 =0
                  GOTO PROBLEM
  
          PROBLEM:
          ROLLBACK TRAN  @TransactionName
      COMMIT
  END TRY
      BEGIN CATCH
          SET @ROWCOUNT1=0
          ROLLBACK TRAN @TransactionName
      END CATCH
  
      IF @rowcount1 =0
          SELECT  -178,@rowcount1;
      ELSE
          SELECT  178,@rowcount1;
      END 
  
  
   END
  </code></pre>
  
  <p>I am passing a datatable to the stored procedure from the C# code.
  When I execute the Sp, No error is thrown But When I call it from the C# code I am getting the Exception </p>
  
  <blockquote>
    <p>Exception: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.</p>
  </blockquote>
  
  <p>Please help and Thanks in advance....</p>