title: MS-SQL Using TRANSACTION with an cursor and inside an stored procedure
body: <p>Can anybody tell what is wrong with this stored procedure?</p>
  
  <p>I want to use a transacation within an stored procedure but if I run this procedure I get the following error message: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION"</p>
  
  <p>I don't get it... I tried so many options with the BEGIN and COMMIT commands...</p>
  
  <p>P.S. It doesn't matter what's inside the while loop, the error still raises.</p>
  
  <p>Any ideas?</p>
  
  <p>The Error raises if I use the query window inside SSMS.</p>
  
  <pre><code>USE db
  GO
  DECLARE @return_value int,
  @str_return nvarchar(100)
  
  EXEC    @return_value = [dbo].[proc_xxxx]
  @guid = 'xxxxxxxxxxxxxxxxx',
  
  @str_return = @str_return OUTPUT
  
  SELECT  @str_return as N'@str_return'
  
  SELECT  'Return Value' = @return_value
  
  GO
  </code></pre>
  
  <p>Maybe this is the reason?</p>
  
  <pre><code>-- Declare Cursor
  DECLARE xx_cursor CURSOR FOR
  SELECT  GUID
  FROM    TABLE;
  
      BEGIN TRY 
  
      BEGIN TRAN
  
          OPEN xx_cursor;
  
          -- Run through
          FETCH NEXT FROM xx_cursor INTO @guid_xx;
  
          WHILE @@FETCH_STATUS = 0
              BEGIN
  
              --DO SOMETHING
  
              FETCH NEXT FROM xx_cursor INTO @guid_xx;
              END;
  
          COMMIT TRAN
  
      END TRY
  
      BEGIN CATCH
  
              ROLLBACK TRAN
  
          SET @str_return = 'Error'
  
      END CATCH
  
      CLOSE xx_cursor;
      DEALLOCATE xx_cursor;
  </code></pre>