TRY/CATCH with ROLLBACK

BEGIN TRY

    BEGIN TRANSACTION

        -- Do your stuff that might fail here

    COMMIT

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0

        ROLLBACK TRAN

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()

        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()

        DECLARE @ErrorState INT = ERROR_STATE()

    -- Use RAISERROR inside the CATCH block to return error 

    -- information about the original error that caused 

    -- execution to jump to the CATCH block. 

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

https://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error

Example with TRY/CATCH and CreateTable

USE [CrouseScripts]

GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateLawsonMirror]    Script Date: 4/10/2020 9:31:28 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[sp_UpdateLawsonMirror]

AS

BEGIN TRY

    BEGIN TRANSACTION

IF OBJECT_ID(N'crousescripts.dbo.tmpDepts', N'U') IS NOT NULL

  drop table crousescripts.dbo.tmpDepts

exec crousescripts.dbo.createtable @path='n:\Deptcode.csv',@outTableName='crousescripts.dbo.tmpDepts'

delete from LawsonMirror.LawsonMirror.depts

INSERT INTO LawsonMirror.LawsonMirror.depts

           (

                                   [COMPANY]

           ,[PROCESS_LEVEL]

           ,[DEPARTMENT]

           ,[R_NAME]

           ,[DEP_DIST_CO]

           ,[DEP_ACCT_UNIT]

           ,[DEP_ACCOUNT]

           ,[DEP_SUB_ACCT]

           ,[SEC_LVL]

           ,[SEC_LOCATION]

           ,[SEGMENT_FLAG]

           ,[ACTIVE_FLAG]

           ,[USER_ID]

           ,[DATE_STAMP]

           ,[TIME_STAMP]

           ,[Col15]

                                   )

                                   select    [COMPANY]

           ,[PROCESS_LEVEL]

           ,[DEPARTMENT]

           ,[R_NAME]

           ,[DEP_DIST_CO]

           ,[DEP_ACCT_UNIT]

           ,[DEP_ACCOUNT]

           ,[DEP_SUB_ACCT]

           ,[SEC_LVL]

           ,[SEC_LOCATION]

           ,[SEGMENT_FLAG]

           ,[ACTIVE_FLAG]

           ,[USER_ID]

           ,[DATE_STAMP]

           ,[TIME_STAMP]

           ,''          from crousescripts.dbo.tmpDepts

IF OBJECT_ID(N'crousescripts.dbo.tmpEmployee', N'U') IS NOT NULL

  drop table crousescripts.dbo.tmpEmployee

exec crousescripts.dbo.createtable @path='n:\employee.csv',@outTableName='crousescripts.dbo.tmpEmployee'

delete from LawsonMirror.LawsonMirror.employee

INSERT INTO LawsonMirror.LawsonMirror.Employee

           ([COMPANY]

           ,[EMPLOYEE]

           ,[LAST_NAME]

           ,[FIRST_NAME]

           ,[MIDDLE_INIT]

           ,[MIDDLE_NAME]

           ,[NICK_NAME]

           ,[ADDR1]

           ,[ADDR2]

           ,[ADDR3]

           ,[ADDR4]

           ,[CITY]

           ,[STATE]

           ,[ZIP]

           ,[EMP_STATUS]

           ,[EIC_STATUS]

           ,[PROCESS_LEVEL]

           ,[DEPARTMENT]

           ,[USER_LEVEL]

           ,[JOB_CODE]

           ,[UNION_CODE]

           ,[SUPERVISOR]

           ,[DATE_HIRED]

           ,[R_SHIFT]

           ,[EXEMPT_EMP]

           ,[ADJ_HIRE_DATE]

           ,[ANNIVERS_DATE]

           ,[CREATION_DATE]

           ,[NBR_FTE]

           ,[PAY_GRADE]

           ,[EMAIL_ADDRESS]

           ,[POSITION]

           ,[WORK_SCHED]

           ,[SUPERVISOR_IND]

           ,[NAME_PREFIX]

           ,[NAME_SUFFIX]

           ,[LAST_NAME_PRE]

           ,[COUNTY])

select [COMPANY]

           ,[EMPLOYEE]

           ,[LAST_NAME]

           ,[FIRST_NAME]

           ,[MIDDLE_INIT]

           ,[MIDDLE_NAME]

          ,[NICK_NAME]

           ,[ADDR1]

           ,[ADDR2]

           ,[ADDR3]

           ,[ADDR4]

           ,[CITY]

           ,[STATE]

           ,[ZIP]

           ,[EMP_STATUS]

           ,[EIC_STATUS]

           ,[PROCESS_LEVEL]

           ,[DEPARTMENT]

           ,[USER_LEVEL]

           ,[JOB_CODE]

           ,[UNION_CODE]

           ,[SUPERVISOR]

           ,[DATE_HIRED]

           ,[R_SHIFT]

           ,[EXEMPT_EMP]

           ,[ADJ_HIRE_DATE]

           ,[ANNIVERS_DATE]

           ,[CREATION_DATE]

           ,[NBR_FTE]

           ,[PAY_GRADE]

           ,[EMAIL_ADDRESS]

           ,[POSITION]

           ,[WORK_SCHED]

           ,[SUPERVISOR_IND]

           ,[NAME_PREFIX]

           ,[NAME_SUFFIX]

           ,[LAST_NAME_PRE]

           ,[COUNTY]

             from crousescripts.dbo.tmpEmployee

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [EMAIL_ADDRESS]='' WHERE [EMAIL_ADDRESS] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET MIDDLE_INIT='' WHERE MIDDLE_INIT is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [UNION_CODE]='' WHERE [UNION_CODE] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [USER_LEVEL]='' WHERE [USER_LEVEL] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [SUPERVISOR_IND]='' WHERE [SUPERVISOR_IND] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [PAY_GRADE]='' WHERE [PAY_GRADE]is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [SUPERVISOR]='' WHERE [SUPERVISOR]is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [NICK_NAME]='' WHERE [NICK_NAME] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [POSITION]='' WHERE [POSITION] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [MIDDLE_NAME]='' WHERE [MIDDLE_NAME] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR1]='' WHERE [ADDR1] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR2]='' WHERE [ADDR2] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR3]='' WHERE [ADDR3] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ADDR4]='' WHERE [ADDR4] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [NAME_PREFIX]='' WHERE [NAME_PREFIX] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [NAME_SUFFIX]='' WHERE [NAME_SUFFIX] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [LAST_NAME_PRE]='' WHERE [LAST_NAME_PRE] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [COUNTY]='' WHERE [COUNTY] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [CITY]='' WHERE [CITY] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [ZIP]='' WHERE [ZIP] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[Employee] SET [STATE]='' WHERE [STATE] is NULL

IF OBJECT_ID(N'crousescripts.dbo.tmphrsuper', N'U') IS NOT NULL

  drop table crousescripts.dbo.tmphrsuper

exec crousescripts.dbo.createtable @path='n:\hrsuper.csv',@outTableName='crousescripts.dbo.tmphrsuper'

delete from [LawsonMirror].[LawsonMirror].[SupervisorLU]

  INSERT INTO  [LawsonMirror].[LawsonMirror].[SupervisorLU] (

       [COMPANY]

      ,[CODE]

      ,[DESCRIPTION]

      ,[EMPLOYEE]

      ,[OBJ_ID]

      ,[EFFECT_DATE]

      ,[ACTIVE_FLAG]

      ,[SUPER_RPTS_TO]

      ,[USER1]

      ,[USER2]

      ,[USER3]

      ,[USER4]

      ,[USER5]

      ,[HSUSET4_SS_SW])

   SELECT [COMPANY]

      ,[CODE]

      ,[DESCRIPTION]

      ,[EMPLOYEE]

      ,[OBJ_ID]

      ,[EFFECT_DATE]

      ,[ACTIVE_FLAG]

      ,[SUPER_RPTS_TO]

      ,[USER1]

      ,[USER2]

      ,[USER3]

      ,[USER4]

      ,[USER5]

      ,[HSUSET4_SS_SW]

                  FROM  crousescripts.dbo.tmphrsuper

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [ACTIVE_FLAG]='' WHERE [ACTIVE_FLAG] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [SUPER_RPTS_TO]='' WHERE [SUPER_RPTS_TO] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER1]='' WHERE [USER1] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER2]='' WHERE [USER2] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER3]='' WHERE [USER3] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER4]='' WHERE [USER4] is NULL

  UPDATE [LawsonMirror].[LawsonMirror].[SupervisorLU] SET [USER5]='' WHERE [USER5] is NULL

IF OBJECT_ID(N'crousescripts.dbo.tmpglnames', N'U') IS NOT NULL

  drop table crousescripts.dbo.tmpglnames

exec crousescripts.dbo.createtable @path='n:\glnames.csv',@outTableName='crousescripts.dbo.tmpglnames'

--select * from crousescripts.dbo.tmpglnames

delete from [LawsonMirror].[LawsonMirror].[GL_Names]

INSERT INTO  [LawsonMirror].[LawsonMirror].[GL_Names] (

       [COMPANY]

      ,[ACCT_UNIT]

      ,[VAR_LEVELS]

      ,[DESCRIPTION]

      ,[CHART_SECTION]

      ,[CURRENCY_CODE]

      ,[PERSON_RESP]

      ,[ACTIVE_STATUS]

      ,[ACCT_GENERATE]

      ,[POSTING_FLAG]

      ,[VAR_LEVEL_DISP]

      ,[LEVEL_DEPTH]

      ,[LEVEL_DETAIL_01]

      ,[LEVEL_DETAIL_02]

      ,[LEVEL_DETAIL_03]

      ,[LEVEL_DETAIL_04]

      ,[LEVEL_DETAIL_05]

      ,[OBJ_ID]

      ,[PARENT_OBJ_ID]

      ,[GLNSET3_SS_SW])

SELECT [COMPANY]

      ,[ACCT_UNIT]

      ,[VAR_LEVELS]

      ,[DESCRIPTION]

      ,[CHART_SECTION]

      ,[CURRENCY_CODE]

      ,[PERSON_RESP]

      ,[ACTIVE_STATUS]

      ,[ACCT_GENERATE]

      ,[POSTING_FLAG]

      ,[VAR_LEVEL_DISP]

      ,[LEVEL_DEPTH]

      ,[LEVEL_DETAIL_01]

      ,[LEVEL_DETAIL_02]

      ,[LEVEL_DETAIL_03]

      ,[LEVEL_DETAIL_04]

      ,[LEVEL_DETAIL_05]

      ,[OBJ_ID]

      ,[PARENT_OBJ_ID]

      ,[GLNSET3_SS_SW]

  FROM crousescripts.dbo.tmpglnames

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [VAR_LEVELS]='' WHERE [VAR_LEVELS] is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [CHART_SECTION]='' WHERE [CHART_SECTION] is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [CURRENCY_CODE]='' WHERE [CURRENCY_CODE] is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [PERSON_RESP]='' WHERE [PERSON_RESP] is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [VAR_LEVEL_DISP]='' WHERE [VAR_LEVEL_DISP] is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET [ACCT_GENERATE]='' WHERE [ACCT_GENERATE] is NULL

/*UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET ='' WHERE  is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET ='' WHERE  is NULL

UPDATE [LawsonMirror].[LawsonMirror].[GL_Names] SET ='' WHERE  is NULL*/

IF OBJECT_ID(N'crousescripts.dbo.tmpJobCodes', N'U') IS NOT NULL

  drop table crousescripts.dbo.tmpJobCodes

exec crousescripts.dbo.createtable @path='n:\jobcodes.csv',@outTableName='crousescripts.dbo.tmpJobCodes'

--select * from crousescripts.dbo.tmpglnames

delete from [LawsonMirror].[Lawson].[JOBCODES]

INSERT INTO  [LawsonMirror].[Lawson].[JOBCODES] (

       [COMPANY]

      ,[JOB_CODE]

      ,[DESCRIPTION]

      ,[RATE_OVERRIDE]

      ,[BEG_SAL_RANGE]

      ,[MID_SAL_RANGE]

      ,[END_SAL_RANGE]

      ,[POINTS]

      ,[EEO_CAT]

      ,[LAST_CMT_SEQ]

      ,[ACTIVE_FLAG]

      ,[ANNUAL_HOURS]

      ,[JOB_CLASS]

      ,[SCHEDULE]

      ,[PAY_GRADE]

      ,[EEO_SUB_CODE]

      ,[EXEMPT]

      ,[WC_CLASS]

      ,[CA_WC_CLASS]

      ,[PAY_RATE]

      ,[EXP_DIST_CO]

      ,[EXP_ACCT_UNIT]

      ,[EXP_ACCOUNT]

      ,[EXP_SUB_ACCT]

      ,[PROCESS_LEVEL]

      ,[DEPARTMENT]

      ,[REPORTS_TO]

      ,[APPROVED_BY]

      ,[TIPPED]

      ,[OBJ_ID]

      ,[MARKET_SALARY]

      ,[CURRENCY_CODE]

      ,[CURR_ND]

      ,[BASE_PAY_RATE]

      ,[BASE_CURRENCY]

      ,[BASE_ND]

      ,[BASE_BEG_SAL]

      ,[BASE_MID_SAL]

      ,[BASE_END_SAL]

      ,[BASE_MARKET]

      ,[SALARY_CLASS]

      ,[WEB_TYPE]

      ,[GENDER_CLASS]

      ,[DIR_IND_JOB]

      ,[OCCUP_GROUP]

      ,[TENURE_ELIG]

      ,[HIGH_ED_CAT]

      ,[HIGH_ED_SUBCAT]

      ,[ACADEMIC_RANK]

      ,[L_INDEX]

      ,[L_ATJBC_SS_SW]

                  )

  SELECT [COMPANY]

      ,[JOB_CODE]

      ,[DESCRIPTION]

      ,[RATE_OVERRIDE]

      ,[BEG_SAL_RANGE]

      ,[MID_SAL_RANGE]

      ,[END_SAL_RANGE]

      ,[POINTS]

      ,[EEO_CAT]

      ,[LAST_CMT_SEQ]

      ,[ACTIVE_FLAG]

      ,[ANNUAL_HOURS]

      ,[JOB_CLASS]

      ,[SCHEDULE]

      ,[PAY_GRADE]

      ,[EEO_SUB_CODE]

      ,[EXEMPT]

      ,[WC_CLASS]

      ,[CA_WC_CLASS]

      ,[PAY_RATE]

      ,[EXP_DIST_CO]

      ,[EXP_ACCT_UNIT]

      ,[EXP_ACCOUNT]

      ,[EXP_SUB_ACCT]

      ,[PROCESS_LEVEL]

      ,[DEPARTMENT]

      ,[REPORTS_TO]

      ,[APPROVED_BY]

      ,[TIPPED]

      ,[OBJ_ID]

      ,[MARKET_SALARY]

      ,[CURRENCY_CODE]

      ,[CURR_ND]

      ,[BASE_PAY_RATE]

      ,[BASE_CURRENCY]

      ,[BASE_ND]

      ,[BASE_BEG_SAL]

      ,[BASE_MID_SAL]

      ,[BASE_END_SAL]

      ,[BASE_MARKET]

      ,[SALARY_CLASS]

      ,[WEB_TYPE]

      ,[GENDER_CLASS]

      ,[DIR_IND_JOB]

      ,[OCCUP_GROUP]

      ,[TENURE_ELIG]

      ,[HIGH_ED_CAT]

      ,[HIGH_ED_SUBCAT]

      ,[ACADEMIC_RANK]

      ,[L_INDEX]

      ,[L_ATJBC_SS_SW]

  FROM crousescripts.dbo.tmpJobCodes

      COMMIT TRAN -- Transaction Success!

END TRY

BEGIN CATCH

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()

        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()

        DECLARE @ErrorState INT = ERROR_STATE()

    IF @@TRANCOUNT > 0

        ROLLBACK TRAN --RollBack in case of Error

    -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH