Need advice on how to handle BEGIN TRY/TRAN in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need advice on how to handle BEGIN TRY/TRAN in SQL

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-11-17 : 17:16:04

[code]Hi,

I need some help with transactional control. If there is any problems occur, I like to rollback back the entire transctions
(Do not call uspKON_ConlogUpdate and abort the UPDATE statement below.)

Is there a better way to handle than the codes below. Any suggestion would greatly appreciate.

EXECute dbo.uspKON_ConlogUpdate @LoanNum = @LoanNum, @EnteredBy = @LoginId, @EntryType = 1, @ConLogTxt = @ConLogTxtOnHold

UPDATE NCLoanMod
SET OnHoldEMailSentDt = GETDATE()
WHERE LoanModId = @LoanModId
AND LoanNum = @LoanNum

Thank you in advance.

--SP

IF OBJECT_ID('v2.spNC_LoanModEMail', 'p') IS NOT NULL
DROP PROCedure v2.spNC_LoanModEMail
GO

CREATE PROCedure v2.spNC_LoanModEMail
(
@LoanModId INT
,@LoginId VARCHAR(50)
)
AS
/***********************************************************************************************
**
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON
SET XACT_ABORT ON
DECLARE @LoanNum VARCHAR(10),
@ReturnStatus BIT,
@KondaurFclAlertsEmail VARCHAR(60),
@PortfolioMgr VARCHAR(60),
@AssetMgr VARCHAR(60),
@PortfolioMgrEmail VARCHAR(60),
@AssetMgrEmail VARCHAR(60),

@ConLogTxtOnHold VARCHAR(5000),
@ConLogTxtNotifyPM VARCHAR(5000),
@ConLogTxtResumefcl VARCHAR(5000),
@ConLogTxtContiOnHold VARCHAR(5000)

DECLARE @FclQuestionAllNo BIT = 1
,@FclNOIFlag BIT = 0
,@CrLf CHAR(2) = CHAR(13) + CHAR(10)

IF OBJECT_ID('Tempdb.dbo.#EMailSendOut', 'u') IS NOT NULL
DROP TABLE #EMailSendOut

CREATE TABLE #EMailSendOut
(
EmailTo VARCHAR(100),
EmailCC VARCHAR(100),
EmailTypeCd VARCHAR(20),
EmailSubject VARCHAR(1000),
EMailBody VARCHAR(2000)
)

SET @LoanNum = ( SELECT LoanNum
FROM NCLoanMod
WHERE LoanModId = @LoanModId )
--SELECT @LoanNum

IF EXISTS ( SELECT 'x'
FROM LoanModificationAnswers
WHERE LoanModId = @LoanModId
AND Response = 'Yes' )
-- If the Response is Yes then @FclQuestionAllNo = 0 else @FclQuestionAllNo = 1
SET @FclQuestionAllNo = 0

IF EXISTS ( SELECT 'x'
FROM ExitInfo
WHERE LoanNum = @LoanNum
AND (InFCflag = 'Yes'
OR (NOISent IS NOT NULL AND NOISent <> '1900-01-01 00:00:00.000')
OR (NOIExpired IS NOT NULL AND NOIExpired <> '1900-01-01 00:00:00.000') )
)
SET @FclNOIFlag = 1

SET @KondaurFclAlertsEmail = ( SELECT Email
FROM GroupEmails
WHERE Name = 'Kondaur Foreclosure Alerts' )

INSERT #EMailSendOut (EmailTo, EmailTypeCd, EmailSubject, EMailBody)
SELECT @KondaurFclAlertsEmail AS 'EmailTo',
'OnHold' AS 'EmailTypeCd',
A.LoanNum + ' - ' + b.BorrLast + ' - North Carolina Loss Mitigation Request Received',
'Loan modification request for ' + @LoanNum + ' in the state of North Carolina has been received on ' + CONVERT(CHAR(10), a.RequestDate, 101) + ' and this loan is in foreclosure. Please stop all foreclosure processes until a final determination has been made.'
FROM NCLoanMod AS a
JOIN KondaurData AS b
ON a.LoanNum = b.LoanNum
WHERE a.LoanModId = @LoanModId
AND a.LoanNum = @LoanNum
AND a.LoanModStarted = 1
AND a.OnHoldEMailSentDt IS NULL
AND @FclQuestionAllNo = 1
AND @FclNOIFlag = 1

SET @ConLogTxtOnHold = ( SELECT EMailBody
FROM #EMailSendOut
WHERE EmailTypeCd = 'OnHold')
--SELECT @ConLogTxtOnHold

IF (@@ROWCOUNT = 1 AND @ConLogTxtOnHold IS NOT NULL)
--BEGIN
BEGIN TRY
BEGIN TRAN
EXECute dbo.uspKON_ConlogUpdate @LoanNum = @LoanNum, @EnteredBy = @LoginId, @EntryType = 1, @ConLogTxt = @ConLogTxtOnHold

UPDATE NCLoanMod
SET OnHoldEMailSentDt = GETDATE()
WHERE LoanModId = @LoanModId
AND LoanNum = @LoanNum

COMMIT TRAN
END TRY
--END

BEGIN CATCH

ROLLBACK TRAN

DECLARE @ErrorMessage VARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH
--end

------------------------------------------------------------------------------
--#2

SELECT @PortfolioMgr = PortfolioMgr
,@AssetMgr = AssetManager
FROM KondaurData
WHERE LoanNum = @LoanNum

SET @PortfolioMgrEmail = ( SELECT Email
FROM Logins
WHERE UserName = @PortfolioMgr )
--SELECT @PortfolioMgrEmail

SET @AssetMgrEmail = ( SELECT Email
FROM Logins
WHERE UserName = @AssetMgr )
--SELECT @AssetMgrEmail

INSERT #EMailSendOut (EmailTo, EmailCC, EmailTypeCd, EmailSubject, EMailBody)
SELECT @PortfolioMgrEmail AS 'EmailTo',
@AssetMgrEmail AS 'EmailCC',
'NotifyPM' AS 'EmailTypeCd',
'Please review ' + a.LoanNum + ' - ' + b.BorrLast + ' - North Carolina Loss Mitigation Request Review',
'Loan modification request for ' + a.LoanNum + ' in the state of North Carolina requires Portfolio Manager attention. Please review and confirm the request.'
FROM NCLoanMod AS a
JOIN KondaurData b
ON a.LoanNum = b.LoanNum
WHERE LoanModId = @LoanModId
AND a.LoanNum = @LoanNum
AND (a.IsApproved = 1 OR a.IsDenied = 1)
AND a.NotifyPMEmailSentDt IS NULL

SET @ConLogTxtNotifyPM = ( SELECT EMailBody
FROM #EMailSendOut
WHERE EmailTypeCd = 'NotifyPM' )

--SELECT @ConLogTxtNotifyPM

IF (@@ROWCOUNT = 1 AND @ConLogTxtNotifyPM IS NOT NULL)
--BEGIN
BEGIN TRY
BEGIN TRAN
EXECute dbo.uspKON_ConlogUpdate @LoanNum = @LoanNum, @EnteredBy = @LoginId, @EntryType = 1, @ConLogTxt = @ConLogTxtNotifyPM

UPDATE NCLoanMod
SET NotifyPMEmailSentDt = GETDATE()
WHERE LoanModId = @LoanModId
AND LoanNum = @LoanNum

COMMIT TRAN
END TRY
--END

BEGIN CATCH
ROLLBACK TRAN

--DECLARE @ErrorMessage VARCHAR(4000)
--DECLARE @ErrorSeverity INT
--DECLARE @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH
--end

------------------------------------------------------------------------------
--#3
--// Resume FCL
INSERT #EMailSendOut (EmailTo, EmailTypeCd, EmailSubject, EMailBody)
SELECT @KondaurFclAlertsEmail AS 'EmailTo',
'Resumefcl' AS 'EmailTypeCd',
a.LoanNum + ' - ' + b.BorrLast + ' - North Carolina Loss Mitigation Request - DENIED',
'Loan modification request for ' + @LoanNum + ' in the state of North Carolina has been Denied on ' + CONVERT(CHAR(10), a.IsDeniedDt, 101) + ' due to ' + ReasonForDenial + '. Please resume all foreclosure processes.'
FROM NCLoanMod AS a
JOIN KondaurData b
ON a.LoanNum = b.LoanNum
WHERE a.LoanModId = @LoanModId
AND a.LoanNum = @LoanNum
AND a.IsPMReviewed = 1
AND a.IsDenied = 1
AND @FclQuestionAllNo = 1 -- Answer in Response = NO return 1 else return 0 Yes.
AND @FclNOIFlag = 1 -- AND (InFCflag = 'Yes'
-- OR (NOISent IS NOT NULL AND NOISent <> '1900-01-01 00:00:00.000')
-- OR (NOIExpired IS NOT NULL AND NOIExpired <> '1900-01-01 00:00:00.000') )
AND a.ResumeFCLEmailSentDt IS NULL

SET @ConLogTxtResumefcl = ( SELECT EMailBody
FROM #EMailSendOut
WHERE EmailTypeCd = 'Resumefcl')
--SELECT @ConLogTxtResumefcl
--SELECT * FROM #EMailSendOut

IF (@@ROWCOUNT = 1 AND @ConLogTxtResumefcl IS NOT NULL)
--BEGIN

BEGIN TRY
BEGIN TRAN
EXECute dbo.uspKON_ConlogUpdate @LoanNum = @LoanNum, @EnteredBy = @LoginId, @EntryType = 1, @ConLogTxt = @ConLogTxtResumefcl

UPDATE NCLoanMod
SET ResumeFCLEMailSentDt = GETDATE()
WHERE LoanModId = @LoanModId
AND LoanNum = @LoanNum

COMMIT TRAN
END TRY
--END

BEGIN CATCH

ROLLBACK TRAN

--DECLARE @ErrorMessage VARCHAR(4000)
--DECLARE @ErrorSeverity INT
--DECLARE @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

------------------------------------------------------------------------------
--#4

--// Continue Hold on.
INSERT #EMailSendOut (EmailTo, EmailTypeCd, EmailSubject, EMailBody)
SELECT @KondaurFclAlertsEmail AS 'EmailTo',
'Contifcl' AS 'EmailTypeCd',
a.LoanNum + ' - ' + b.BorrLast + ' - North Carolina Loss Mitigation Request - APPROVED',
'Loan modification request for ' + @LoanNum + ' in the state of North Carolina has been Approved on ' + CONVERT(CHAR(10), a.IsApprovedDt, 101) + ' with the following terms:' + @CrLf + 'Interest Rate: ' + CAST(a.InterestRate AS VARCHAR(10)) + @CrLf +
'Principle Amount: ' + CAST(a.PrincipalAmount AS VARCHAR(20)) + @CrLf + 'Term: ' + CAST(a.Term AS VARCHAR(10)) + @CrLf + 'Loan Type: ' + a.LoanType + @CrLf + 'P&I Amount: ' + CAST(a.PAndIAmount AS VARCHAR(10))
FROM NCLoanMod AS a
JOIN KondaurData b
ON a.LoanNum = b.LoanNum
WHERE a.LoanModId = @LoanModId
AND a.LoanNum = @LoanNum
AND a.IsApproved = 1
AND a.IsPMReviewed = 1
AND @FclQuestionAllNo = 1 -- Answer in Response = NO return 1 else return 0 Yes.
AND @FclNOIFlag = 1 -- AND (InFCflag = 'Yes'
-- OR (NOISent IS NOT NULL AND NOISent <> '1900-01-01 00:00:00.000')
-- OR (NOIExpired IS NOT NULL AND NOIExpired <> '1900-01-01 00:00:00.000') )
AND a.ContinueHoldEmailSentDt IS NULL

SET @ConLogTxtContiOnHold = ( SELECT EMailBody
FROM #EMailSendOut
WHERE EmailTypeCd = 'Contifcl' )
--SELECT @ConLogTxtContiOnHold
--SELECT * FROM #EMailSendOut

IF (@@ROWCOUNT = 1 AND @ConLogTxtResumefcl IS NOT NULL)
--BEGIN

BEGIN TRY
BEGIN TRAN

EXECute dbo.uspKON_ConlogUpdate @LoanNum = @LoanNum, @EnteredBy = @LoginId, @EntryType = 1, @ConLogTxt = @ConLogTxtContiOnHold

UPDATE NCLoanMod
SET ContinueHoldEmailSentDt = GETDATE()
WHERE LoanModId = @LoanModId
AND LoanNum = @LoanNum

COMMIT TRAN
END TRY
--END

BEGIN CATCH

ROLLBACK TRAN

--DECLARE @ErrorMessage VARCHAR(4000)
--DECLARE @ErrorSeverity INT
--DECLARE @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

SELECT EmailTo
,COALESCE(EmailCC, ' ') AS 'EmailCC'
,EmailSubject
,EMailBody
FROM #EMailSendOut

GO[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources