Error trapping and roll back in stored procedure SQL server

I have a stored procedure (pinned to the bottom of this post) that does 4 or 5 major sql statements. All works great(thanks angellll) anyway. There are a number of things I would like to do to help icnrease the stability of this procedure

1-Roll back in case of error
2-Error repoting to a table called tbl_log

Could anyone help with regards to roll back in case one of the SQL statements in the stored procedure fail?

Also how would I capture this error and post it to a log table?

the stored procedure is here( i have put a line "=======here====" where I want to check for errors occuring) :-

CREATE  PROCEDURE dbo.prc_Import_Order (@batchNo varchar(9))
AS
SET XACT_ABORT ON
DECLARE @TSQL varchar(8000)
DECLARE @sFrom varchar(400)
DECLARE @sTo varchar(400)
DECLARE @sCC varchar(400)
DECLARE @smessage varchar(8000)
DECLARE @sServer varchar(400)
DECLARE @sSubject varchar(400)
DECLARE @tracker int
DECLARE @Prevent varchar(6)
PRINT 'prc_import order'

/*
======================
Start Batch log transaction
======================
*/

--Seems to need this intialisation to work
SET @Prevent = ''

--check if there is an entry for this batch already in the log tables
SELECT @Prevent =  (SELECT log_id from  [GLOAD].[DBO].[TBL_LOG] WHERE log_batch = @BatchNo And log_type = 1)

--If there isnt an entry then we are good to process the rest
IF @PREVENT is null
BEGIN
      PRINT 'ITS NULL WE R HAPPY'
      insert INTO [GLOAD].[DBO].[TBL_LOG] (log_type, log_batch, log_sDate) VALUES (1, @BatchNo, getdate())
      SELECT @Tracker = (SELECT log_id from  [GLOAD].[DBO].[TBL_LOG] WHERE log_batch = @BatchNo)
      
/*
=========================================================
Here we grab the header information and put it to the table tbl_OHeader

      *Still missing customer information - DONE
      *Missing Text for the customer
=========================================================


PRINT(@BATCHNO)
*/


SET @TSQL        = 'INSERT INTO [gload].[dbo].[tbl_OHeader] (BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BATCH_DATE,BATCH_TIME,SBUSY,ISHRR,GORTP,EORNO,ECSTN9,EDTNO5,FSOCN,FSSDN,FSTXT,GPLID1,ECCODA,USXRTA,FSCPR1,ESOEB,[@SDOE],TSTOE,DSCRF1,DSCNT1,[@SRDD],GRUTE1,SSOVR,ESOVB,[@SOVD],TSOVT,SSCCH,[@SCHD],ESCHB,[@SCRD],ESCRB,[@PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSWDC1,RWDIS1,[@DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC1,ESRPP,ESRPS,ISFTR,EORNP,FBORD,TSRDD3,FCRSK1,FSHB1,FSHB2,FSHB3, ' +
            'FSHB4,FSHB5,FSHM1,FSHM2,FSHM3,GSPID1,FASOO,SPOAS,SWOAS,ECSTNC,EDTNOC,DDNAM,ADAD1,ADAD2,ADAD3,ADAD4,ADPC1,ADPC2,DCNAM, ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2) '+
            'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@BATCH_DATE],[@BATCH_TIME],SBUSY,ISHRR,GORTP,EORNO,ECSTN9,EDTNO5,FSOCN,FSSDN,FSTXT,GPLID1,ECCODA,USXRTA,FSCPR1,ESOEB,[@SDOE],TSTOE,DSCRF1,DSCNT1,[@SRDD],GRUTE1,SSOVR,ESOVB,[@SOVD],TSOVT,SSCCH,[@SCHD],ESCHB,[@SCRD],ESCRB,[@PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSWDC1,RWDIS1,[@DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC1,ESRPP,ESRPS,ISFTR,EORNP,FBORD,TSRDD3,FCRSK1,FSHB1,FSHB2,FSHB3,FSHB4,FSHB5,FSHM1,FSHM2, '+
            'FSHM3,GSPID1,FASOO,SPOAS,SWOAS,ECSTNC,EDTNOC,DDNAM,ADAD1,ADAD2,ADAD3,ADAD4,ADPC1,ADPC2,DCNAM, ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2 FROM Openquery(BS01BAA, '+
            '''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
            'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
            'INNER JOIN GML01A.GLOAD_CUST T2 ON T1.ECSTN9 = T2.ECSTNC AND T1.EDTNO5 = T2.EDTNOC '+
            'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO'

PRINT @TSQL

EXEC (@TSQL)


/*
-------------------------------------------------------------------------------------------------------------------
End the header output SQL
-------------------------------------------------------------------------------------------------------------------
*/
/*
==========================================================================
Here we grab the line/line Extra/product detail  information and put it to the table tbl_OLine
==========================================================================
*/

SET @TSQL        = 'INSERT INTO [gload].[dbo].[tbl_OLine] (BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BATCH_DATE,BATCH_TIME, SDRCD,GORTP8,EORNO8,LORDS4,[@SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,QPODB1,QPODC1,FDEFC,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB1,FSSB2,FSSB3,FSSM1,FSSM2,SDRCD_old,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,[£UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[£TOVL],CTOVL,[£TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,EOROR1, '+
            'EITM1B,EITM2B,EITM3B,EITM4B,EITM5B,[@SRDD1],[@SODD],EVATCB,GCNRC2,SASPO1,SASWO1,ESRPP1,ESRPS1,ISFTR1,FSRFD,TSRDD2,FSDB1,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,FWOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITEM,DITMD,UOMTU,MUDN3,QGWGT)  '+

            'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@BATCH_DATE],[@BATCH_TIME],SDRCD,GORTP8,EORNO8,LORDS4,[@SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,QPODB1,QPODC1,FDEFC,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB1,FSSB2,FSSB3,FSSM1,FSSM2, SDRCD,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,[£UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[£TOVL],CTOVL,[£TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,EOROR1,EITM1B,EITM2B,EITM3B,EITM4B, '+
            'EITM5B,[@SRDD1],[@SODD],EVATCB,GCNRC2,SASPO1,SASWO1,ESRPP1,ESRPS1,ISFTR1,FSRFD,TSRDD2,FSDB1,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,FWOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITEM,DITMD,UOMTU,MUDN3,QGWGT FROM Openquery(BS01BAA, '+
            '''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
            'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
            'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO = T2.EORNO8 '+
            'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 = T3.EORNO1 AND t2.LORDS4 = T3.LORDS '+
            'INNER JOIN GML01A.GLOAD_ITEM T4 ON T2.EITN12 = T4.ITEM '+
            'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO, LORDS4'

/*PRINT @TSQL*/

EXEC (@TSQL)
/*
-------------------------------------------------------------------------------------------------------------------
End the Line output SQL
-------------------------------------------------------------------------------------------------------------------
*/

/*
==========================================================================
 Here we grab the order text table to output ready for later when we grab that data and put it
 onto the paperwork
==========================================================================
-Old sql

SET @TSQL        = 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO,  EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR)  '+
            'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM Openquery(BS01BAA, '+
            '''SELECT BATCH_NUMBER, EORNO3, LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM GML01A.YSBTCHP_LINK TM '+
            'INNER JOIN BS01BAA.BSSOTRP T1 ON TM.BATCH_OI_NO = T1.EORNO3 '+
            'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO3, LORDS1'
*/

SET @TSQL        = 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO,  EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR)  '+
            'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM Openquery(BS01BAA, '+
            '''SELECT BATCH_NUMBER, EORNO3, LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM GML01A.YSBTCHP_LINK TM '+
            'INNER JOIN GML01A.GLOAD_TEXT T1 ON TM.BATCH_OI_NO = T1.EORNO3 '+
            'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO3, LORDS1'


EXEC (@TSQL)

SET @TSQL        = 'INSERT INTO [gload].[dbo].[tbl_CText] (IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NO)  '+
            'SELECT IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NUMBER FROM Openquery(BS01BAA, '+
            '''SELECT BATCH_NUMBER, IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NUMBER FROM GML01A.YSBTCHP_LINK TM '+
            'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
            'INNER JOIN BS01BAA.BCTEXTP T2 ON T1.ECSTN9 = T2.IACCK AND T1.EDTNO5 = EDTNP4 '+
            'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY INTRR3'


EXEC (@TSQL)

/*
-------------------------------------------------------------------------------------------------------------------
End the Order text output SQL
-------------------------------------------------------------------------------------------------------------------
*/

/*
==========================================================================
 Here we update the YSBTCHP_DONE table to show we have processed the batch
      *If somone tries a batch for a second time, it wont proces it above but will
            add another entry into this table, must find a way to stop that!

==========================================================================
*/
SET @TSQL = 'INSERT INTO Openquery(GMP01A, '+
          '''SELECT DONE_BTCH FROM GMP01A.YSBTCHP_DO WHERE 1=0 '+
          ''') VALUES ('+@BATCHNO+')'
EXEC (@TSQL)


/*

-------------------------------------------------------------------------------------------------------------------
End the header output SQL NOT USED YET PROBABLY NEVA
-------------------------------------------------------------------------------------------------------------------
*/

SET @TSQL =       'SELECT * FROM Openquery(BS01BAA, '+
            '''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
            'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
            'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO = T2.EORNO8 '+            
            'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 = T3.EORNO1 AND t2.LORDS4 = T3.LORDS '+
            'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO, LORDS4'

/*
==========================================================================
Send the email out to the relelvant selected people
==========================================================================
*/

--Create a temp table to output the multi line order info
--Concat it together then save it to a variable
declare @tab table (i int identity , biatch nvarchar(100), border nvarchar(900), [@SDOE] varchar(10), dcnam varchar(900), [@SRDD] varchar(10))
declare @Rows int
declare @sORder nvarchar(50)
declare @sDate1 nvarchar(10)
declare @sDcnam varchar(900)
declare @sDate2 nvarchar(10)
declare @sconcat varchar(8000)
declare @columnH nvarchar(200)

--Insert into the temp table the rows that require concatanating
      INSERT INTO @Tab
      SELECT batch_number, batch_oi_no, [@SDOE],dcnam,[@SRDD]
      FROM tbl_OHeader
      WHERE BATCH_NUMBER = @BatchNo

--get the amount of rows affected
      SELECT @Rows = @@ROWCOUNT
      SET @sConcat = ''
WHILE @Rows > 0
BEGIN
      --This basiclly grabs all the fields needed and concatenates them together in a loop, subqueries do not support multi line returns from a select
          Select @sOrder = border from @Tab WHERE i = @Rows AND biatch = @batchNo
      Select @sDate1 = SUBSTRING ( Convert (VarChar(10), [@SDOE] + 19000000 ) , 7 , 2 ) + '/' +
      SUBSTRING ( Convert (VarChar(10), [@SDOE] + 19000000 ) , 5 , 2 ) + '/' +
      SUBSTRING ( Convert (VarChar(10), [@SDOE] + 19000000 ) , 1 , 4 )  from @Tab WHERE i = @Rows AND biatch = @batchNo
      Select @sDcnam = dcnam from @Tab WHERE i = @Rows AND biatch = @batchNo
      Select @sDate2 = SUBSTRING ( Convert (VarChar(10), [@SRDD] + 19000000 ) , 7 , 2 ) + '/' +
      SUBSTRING ( Convert (VarChar(10), [@SRDD] + 19000000 ) , 5 , 2 ) + '/' +
      SUBSTRING ( Convert (VarChar(10), [@SRDD] + 19000000 ) , 1 , 4 ) from @Tab WHERE i = @Rows AND biatch = @batchNo
      SET @SconCat = @sConcat + ' ' + @Sorder + '  ' + @sDate1+ ' ' + @sDcnam+ ' ' + @Sdate2 + char(13)  + char(10)
      SET @Rows = @Rows -1
END

      SET @columnH = char(13)  + char(10) + 'Batch number = ' + @BatchNo + char(13)  + char(10) + char(10) + '---===================Batch Information===================---'  + char(10) + 'Order No Order Date Customer                       Req Date'
--Grab the individual field to be able to send the email
      SELECT @sFrom = (SELECT email_From FROM tbl_email WHERE email_arg = @BatchNo)
      SELECT @sTo = (SELECT email_to FROM tbl_email WHERE email_arg = @BatchNo)
      SELECT @sCc = (SELECT email_cc FROM tbl_email WHERE email_arg = @BatchNo)
      SELECT @sMessage = (SELECT email_text FROM tbl_email WHERE email_arg = @BatchNo)
      SELECT @sSubject = (SELECT email_Subject FROM tbl_email WHERE email_arg = @BatchNo)
--concat the batch number to the end of the subject line
      SET @sSUBJECT = @sSubject + ' - ' + @BatchNo + ' -'
--Concat the order info to the end of the email
      SET @sMessage = @sMessage + char(13)  + char(10) + @columnH + char(13)  + char(10) + @sConCat
--go go email woohoo \o/
      EXEC prc_send_email @sFROM,@sfrom, @sTO, @sCC,@sSubject,@smessage,'GMEXCH1'

/*
======================
End Batch log transaction
======================
*/
Update [GLOAD].[DBO].[TBL_LOG] SET log_eDate = getdate() WHERE log_id = @TRACKER and log_type = 1

END
GO
Start Free Trial