title: Managing transaction in stored procedure with Dynamic SQL
body: <p>I have a stored procedure with Dynamic SQL. Is it possible to include a batch of dynamic SQL inside an explicit transaction with <code>COMMIT</code> or <code>ROLLBACK</code> depending on the value of <code>@@ERROR</code>? 
      Following similar  stored procedure. It is simplified in order to demonstration purpose.</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sp_Example] 
  AS 
  BEGIN
      BEGIN TRANSACTION
      DECLARE @ID VARCHAR(10)
      INSERT INTO [dbo].[Deparment] (Name,Location,PhoneNumber) VALUES ('DeparmentName','DeparmentLocation','0112232332')
  
      SELECT @ID =SCOPE_IDENTITY()
  
      IF @@ERROR &lt;&gt; 0
      BEGIN
             ROLLBACK
             RAISERROR ('Error in Inserting Deparment.', 16, 1)
             RETURN
     END
              SET @InsertQuery = '
                                  DECLARE @Name varchar(100)
  
                                  SELECT  @Name = Name
                                  FROM    dbo.[Deparment]
                                  WHERE   DepartmentId= ''' + @ID +'''
  
                                  INSERT INTO [dbo].[Employee](Name,Age,Salary,DepartmentName)VALUES(''EMPLOYEE NAME'',''25'',''200000'','''+@NAME'')'' 
  
              EXEC(@InsertQuery)
      IF @@ERROR &lt;&gt; 0
      BEGIN
             ROLLBACK
             RAISERROR ('Error in Inserting Employee.', 16, 1)
             RETURN
      END
  
     COMMIT
  END
  </code></pre>
  
  <p>Does outer Transaction scope applies to Dynamic query ?</p>