CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Stored procedure with Try Catch Block is not working

I have to write a stored procedure to copy one table's data into another. If any corrupt records (eg: null records) appear during copying, those records need to bypassed and the copying should continue with next record.

I have columns in destination table to identity which offices are copied and which are not copied. I have tried below stored procedure with try catch block:

CREATE TABLE OFFICE_new 
(
    id int identity, 
    officeid int null, 
    ok int,
    notok int
)

CREATE TABLE OFFICE_old 
( 
    id int identity, 
    officeid int null
)

insert into OFFICE_old (officeid)
values (1000),(2000),(3000),(4000),(null),(6000)
go

alter procedure exception_check
as
begin
   begin try
   begin tran 
        insert into OFFICE_new (officeid)
            select officeid
            from OFFICE_old

        if @@error = 0
        begin
            update OFFICE_new 
            set ok = 1

            commit tran 
        end
    end try
    begin  catch
        insert into OFFICE_new (officeid,notok)
            select officeid, 1
            from OFFICE_old
        commit tran
    end catch
end

This stored procedure should insert copied offices with 'ok' = 1 and failed offices with 'notok' = 1. but it isn't working.

Thanks for help.

Upvotes: 0

Views: 903

Answers (2)

Rahul Maurya
Rahul Maurya

Reputation: 187

remove @@error statement and alter procedure as:

alter procedure exception_check
as
begin
begin try
begin tran 
        insert into OFFICE_new (officeid,notok,ok)
        select officeid,0 as notok,1 as ok
        from OFFICE_old
        commit tran 
    end try
    begin  catch
          insert into OFFICE_new (officeid,notok,ok)
        select officeid,1 as notok,0 as ok
        from OFFICE_old
    commit tran
    end catch
end

Upvotes: 1

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

You have multiple things wrong with your query.

First your @@error check seems incorrect. Are you sure you're not after @@error = 0?

Also your commit statement is located inside the IF so if you do not reach the IF - you'll never commit.

 if @@error <>0
        begin
        update OFFICE_new set ok = 1
        commit tran /*<---- will not commit if you do not reach */
        end

Also - in your catch you still try to insert that which went wrong in your transaction, so you'll get another error. You're trying to insert NULL into officeid.

 insert into OFFICE_new (officeid,notok)
        select officeid, 1
        from OFFICE_old /* one of the values are NULL */

So that'll fail as well and therefore not set 'notok'

Upvotes: 1

Related Questions