using try catch for update

Question

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 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 1

Alternative: how about using transactions and @@IDENTITY ?

DECLARE @problemClientID INT
BEGIN TRANSACTION

    UPDATE ... --etc

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION
        SET @problemClientID = @@IDENTITY
        PRINT N'There was a problem...' --etc
    END
    ELSE
    BEGIN
        -- transaction was a success, do more stuff?
    END
COMMIT TRANSACTION

Answer 2

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.

Answer 3

How does the body of procedure look like?

Are you using a cursor?