Will Begin Try End Try, Begin catch End Catch won't work with "union all"

Transact-SQL
1

Sql Server 2008

First I created the following procedure with "union all" and faced issue
it doesn't rollback all transactions when there is error.

alter procedure [dbo].[Replicate_Employees_Saad_test] as
BEGIN  
BEGIN TRY
		BEGIN TRANSACTION;

set nocount on
delete from kpi.dbo.employees_test
where dontdelete=0

insert kpi.dbo.employees_test (
 Branch
,Card_Code
,Emp_Name 
,DeptName 
,Designation 
,grade 
,left_tag
,active 
)

select 
 Branch = 'D53'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfd53_sql.dbo.employee e (nolock)
join payfd53_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfd53_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000

union all

select 
 Branch = 'A33'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfgrmn_sql.dbo.employee e (nolock)
join payfgrmn_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfgrmn_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000

END TRY


BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT N'The transaction is committable. Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END 


--Select * from employees_test

I had to change my procedure.
I removed union all and it worked fine.

alter procedure [dbo].[Replicate_Employees_Saad_test] as
BEGIN  
BEGIN TRY
		BEGIN TRANSACTION;

set nocount on
delete from kpi.dbo.employees_test
where dontdelete=0

insert kpi.dbo.employees_test (
 Branch
,Card_Code
,Emp_Name 
,DeptName 
,Designation 
,grade 
,left_tag
,active 
)

select 
 Branch = 'D53'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfd53_sql.dbo.employee e (nolock)
join payfd53_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfd53_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000

insert kpi.dbo.employees_test (
 Branch
,Card_Code
,Emp_Name 
,DeptName 
,Designation 
,grade 
,left_tag
,active 
)

select 
 Branch = 'A33'
,Card_Code = e.card_code
,Emp_Name = e.emp_name
,DeptName = p.dept_name
,Designation = g.designation
,grade = g.grade
,left_tag = e.left_tag
,active = e.active
from payfgrmn_sql.dbo.employee e (nolock)
join payfgrmn_sql.dbo.department p on (e.dept_code = p.dept_code)
join payfgrmn_sql.dbo.designation g on (e.desig_code = g.desig_code)
where 1=1
and e.gross_pay >=50000

END TRY


BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT N'The transaction is committable. Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END 


--Select * from employees_test
2

why are you checking XACT_STATE() to perform rollback or commit? if you get into catch, then it has errored. So, something more like this:

BEGIN TRY
   BEGIN TRANSACTION;
      delete....
      insert....
  COMMIT
END TRY
BEGIN CATCH
  if @@Trancount > 0
     Rollback
  Error messaging.......
END CATCH
1 Like
3

Because I copied the syntax from net and I don't know much about xact_state()

I have changed the begin and try block as you suggested.
It is working as expected.

Thanks @mike01

4

Wow, I'll be gentle before others jump to beat you up. Getting syntax, etc from the web is fine, but you need to understand exactly what it is doing before you implement it anywhere, even dev.

2 Likes