OK, I have never been good about putting error handling in my code. I'm not proud of that, I know it is not best practice, so my question is: Will this do what I am thinking it will? I am thinking this code will insert from the raw table into the history table and then, as long as there are no errors it will delete the rows from the raw table.
Thanks!
wb
Code:
ALTER PROCEDURE [ProcessCoreWithNoDetails]
@ThisISN int
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.History (ThisISN, EntityISN, AccountISN, TransactionCodeId, [EntityType_CategoryTypeId], Amount, TransactionDate, Importdate, PacketId, StatusId, PacketIdGL)
SELECT core.ThisISN, ea.EntityISN, ea.AccountISN, TransactionCodeId, EntityType_CategoryTypeId, ISNULL(CAST(Credit AS MONEY), CAST(Debit AS MONEY) * -1), CONVERT(DATETIME, PacketDate + ' ' + PacketHour + ':00:00', 120), GETDATE(), PacketId, 4, PacketIdGL
FROM CoreRaw core JOIN TransactionCode code
ON core.TxCode = code.TxCode JOIN dbo.EntityAccounts ea
ON (ea.AccountNumber = concat(core.BaseAccountNumber,core.AccountTypeNumber) OR ea.AccountNumber = core.BaseAccountNumber)
WHERE code.HasChildData = 0
AND core.ThisISN = @ThisISN
DELETE core FROM CoreRaw core JOIN TransactionCode code
ON core.TxCode = code.TxCode JOIN dbo.EntityAccounts ea
ON ea.AccountNumber = concat(core.BaseAccountNumber,core.AccountTypeNumber) OR ea.AccountNumber = core.BaseAccountNumber
WHERE code.HasChildData = 0
AND core.ThisISN = @ThisISN
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
;THROW
END CATCH
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
;THROW
END CATCH
Thanks!
wb