Bob Cummings Bob Cummings - 2 months ago 8
SQL Question

Using try/catch for update

Because I am a newbie I am trying to log out any errors that may occur with stored procedures I write. I understand Try/Catch in SQL 2005 and error_procedure(), ERROR_MESSAGE() and the other built in functions. What I can't figure out how to do is capture what record caused the error on an update.

I could probably use a cursor and loop through and update a row at a time. Then in the loop set a value and report on that value. But that seems to defeat the purpose of using SQL.

Any ideas or pointer on where to research this issue greatly appreciated. I do not fully understand RowNumber() could I use that somehow? Kind of grasping at straws here.

cheers and thanks

Bob

I am using SQL 2005.

Edit

I really do not want to use transactions for most of this, as it is just for reporting purposes. So an example of what I am doing is:

/******************************************************************************
Now get update the table with the current worker. That depends on the
current status of the loan.
******************************************************************************/

UPDATE #table SET currWorker = tblUser.UserLogonName
FROM tblUser
JOIN tblLoanInvolvement ON tblLoanInvolvement.invlUnderwriterDeptID = tblUser.userID
WHERE tblLoanInvolvement.LOANid = #table.loanid
AND #table.currstatus in('R_UW_Approved','R_Submitted to Underwriting')


UPDATE #table SET currWorker = tblUser.UserLogonName
FROM tblUser
JOIN tblLoanInvolvement ON tblLoanInvolvement.invlProcessorID = tblUser.userID
WHERE tblLoanInvolvement.LOANid = #table.loanid
AND #table.currstatus in('R_UW Approved With Conditions','R_Loan Resubmitted','R_UW_Suspended','R_Submitted to Processing')

UPDATE #table SET currWorker = tblUser.UserLogonName
FROM tblUser
JOIN tblLoanInvolvement ON tblLoanInvolvement.invlCloserID = tblUser.userID
WHERE tblLoanInvolvement.LOANid = #table.loanid
AND #table.currstatus in('R_Docs Out','R_Ready to Close','R_Scheduled to Close and Fund','Scheduled To Close')


So if one row does not update correctly I do not want to loose the whole thing. But it would be very handy to know the value of #table.loanid that caused the problem.

Thanks for your time.

Answer

A try/catch block like this...

BEGIN TRY
    -- Your Code Goes Here --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
END CATCH

...is going to help you locate the problem in your SQL code. If this was in a stored procedure you could also return the parameters (i.e. add a SELECT @RecordID AS [RecordID] to that list in the catch block). Moving forward though, if you are running into problems with the actual data I would encourage you to look at adding foreign keys and other constraints to protect the logical integrity of your database. Ideally at a minimum you cannot put data into the database which will break your stored procedures.

EDIT

Refering to you're most recent edits, if you put the UPDATE inside a stored procedure and catch the error, then replace your update series with calls to that procedure the remaining updates would continue, and you could return/track/log the error within the SP's catch block however you wished to.