Below is my procedure in SQL Server 2005

```
PROCEDURE [dbo].[sp_ProjectBackup_Insert]
    @prj_id bigint
    AS
    BEGIN
     DECLARE @MSG varchar(200)
     DECLARE @TranName varchar(200)
     DECLARE @return_value int

    -- 1. Starting the transaction 
     begin transaction @TranName

    -- 2. Insert the records

     SET IDENTITY_INSERT [PMS_BACKUP].[Common].[PROJECT] ON  INSERT INTO [PMS_BACKUP].[Common].[PROJECT] ([PRJ_ID],[PRJ_NO1],[PRJ_NO2],[PRJ_NO3],[PRJ_DESC],[IS_TASKFORCE],[DATE_CREATED],[IS_APPROVED],[DATE_APPROVED],[IS_HANDEDOVER],[DATE_HANDEDOVER],[DATE_START],[DATE_FINISH],[YEAR_OF_ORDER],[CLIENT_DETAILS],[SCOPE_OF_WORK],[IS_PROPOSAL],[PRJ_MANAGER],[PRJ_NAME],[MANAGER_VALDEL],[MANAGER_CLIENT],[DEPT_ID],[locationid],[cut_off_date]) SELECT * FROM  [pms].[Common].[PROJECT]  T WHERE T.PRJ_ID =  (@prj_id) SET IDENTITY_INSERT [PMS_BACKUP].[Common].[PROJECT] OFF        IF @@ERROR <> 0 GOTO HANDLE_ERROR
      SET IDENTITY_INSERT [PMS_BACKUP].[Common].[DEPARTMENT_CAP] ON  INSERT INTO [PMS_BACKUP].[Common].[DEPARTMENT_CAP] ([CAP_ID],[DEPT_ID],[PRJ_ID],[IS_CAPPED],[DATE_CAPPED],[CAPPED_BY],[CAP_APPROVED_BY],[STATUS],[UNCAPPED_BY],[DATE_UNCAPPED],[DESCRIPTION],[UNCAP_APPROVED_BY],[LOCATIONID]) SELECT * FROM  [pms].[Common].[DEPARTMENT_CAP]  T WHERE T.PRJ_ID =  (@prj_id) SET IDENTITY_INSERT [PMS_BACKUP].[Common].[DEPARTMENT_CAP] OFF        IF @@ERROR <> 0 GOTO HANDLE_ERROR
       INSERT INTO [PMS_BACKUP].[Common].[DOC_REG]  SELECT * FROM  [pms].[Common].[DOC_REG]  T WHERE T.PRJ_ID =  (@prj_id)    IF @@ERROR <> 0 GOTO HANDLE_ERROR 

    -- 3. Commit transaction

     COMMIT TRANSACTION @TranName;

        return @@trancount;

    HANDLE_ERROR: 
     rollback transaction @TranName
     RETURN 1 
    END

```

and the issue is even if the first insert query fails, its not stopping the processing and resume the rest of the insert queries. The return value I am getting is 1, but in the results window I can see the log like this

> 
> (0 row(s) affected) Msg 2627, Level
>  14, State 1, Procedure
>  sp\_ProjectBackup\_Insert, Line 35
>  Violation of PRIMARY KEY constraint
>  'PK\_PROJECT'. Cannot insert duplicate
>  key in object 'Common.PROJECT'. The
>  statement has been terminated.
> 
> 
> (0 row(s) affected)
> 
> 
> (0 row(s) affected)
> 
> 
> 

I thought the `return 1` will make the exit from error handling code but not happening. Any problem with my error handling?