Hey guys, just had a read up on error handling in T-SQL which confused the hell out of me... As far as I can tell though, this code should be ok, not commiting any part of the transaction if there is any kind of error... Thanks in advance
Code:
BEGIN TRY
--Find out if there's any banking sheets to process
IF (SELECT TOP 1 arm_banking_sheet_no
FROM Compass.dbo.tbl_fin_all_received_monies
WHERE arm_banked_date > N'2012/06/01'
AND arm_lbox_batch IS NULL) IS NOT NULL
BEGIN
--Get a list of all the banking sheets we want to process
SELECT SUM(arm_amount) AS amount
,arm_banking_sheet_no AS bsheet
,arm_banking_book AS book
INTO #bsheets
FROM Compass.dbo.tbl_fin_all_received_monies
WHERE arm_banked_date > N'2012/06/01'
AND arm_lbox_batch IS NULL
AND arm_action = 'bank'
AND arm_type <> 'cc'
GROUP BY arm_banking_sheet_no, arm_banking_book
BEGIN TRANSACTION
--Create a lockbox to associate to the bsheets
INSERT INTO nw_lockbox_control (lbox_batch_value, lbox_bsheet_count)
SELECT SUM(amount), COUNT(*)
FROM #bsheets
--And make the link from bsheets to the new lockbox batch
UPDATE f
SET arm_lbox_batch = @@IDENTITY
FROM Compass.dbo.tbl_fin_all_received_monies f
INNER JOIN #bsheets s
ON f.arm_banking_sheet_no = s.bsheet
AND f.arm_banking_book = s.book
COMMIT TRANSACTION
DROP TABLE #bsheets
END
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH