title: Can there be multiple commits to one transaction regardless of scope of while loop
body: <p>Following is a SQL Server stored procedure. There is on start of transaction and there are two commits as you can see below. Is this valid (start is in while loop and 1st commit is in same while loop, but 2nd commit is in 2nd while loop)? If not what could be the solution to do it? </p>
  
  <p>Please help.</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DELETE_COBOC_DATA')
  DROP PROCEDURE DELETE_COBOC_DATA
  GO
  
  CREATE PROCEDURE DELETE_COBOC_DATA @ORGDN VARCHAR(100), @CHUNK VARCHAR(10) 
  AS
  BEGIN
      -- some code that executes before while           
      WHILE (@NUM_ROWS_TMPTRADMIN &gt; 0) 
      BEGIN               
  
          BEGIN TRANSACTION
              -- executes some code
          COMMIT TRANSACTION                              
      END
      -- some more code
      WHILE @NUM_ROWS_TMPDIR &gt; 0
      BEGIN
          -- code code code
          COMMIT TRANSACTION
          -- code code code
      END
      -- some more code here as well
  END
  </code></pre>
  
  <p>As I know this is allowed in MySQL</p>