Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Error Condition for Updates

    Hi,

    I have a series of Updates that I perform on 7 tables. I would like to execute these updates on a nighly basis via a job. I have the update statements lined up in a stored proc.

    So I have -

    Create Proc Update
    AS
    SET NOCOUNT ON

    DECLARE @RETURNVALUE int
    DECLARE @ERRORMESSAGETXT varchar(510)
    DECLARE @ERRORNUM int

    set @RETURNVALUE = 0

    BEGIN ---Start here

    Update table1
    Update table2
    .
    .
    .
    Update table7

    SELECT @ERRORNUM = @@ERROR
    IF @ERRORNUM = 0
    SELECT @RETURNVALUE = 0
    ELSE
    BEGIN
    SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
    WHERE error = @@ERROR
    RAISERROR (@ERRORMESSAGETXT, 16, 1)
    SELECT @RETURNVALUE = 1
    END
    RETURN @RETURNVALUE
    END --- END HERE

    ---

    Is the error checking at the appropriate place? Should I have error conditions after every update? If yes then what condition do I check for?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    @@ERROR only shows the result of the most recent statement. It is cleared for the next statement. Therefor your code is only going to indicate whether an error occured on Update Table7.

    You will need to check for an error after each critical step, and then store the results of the error check. You can store it in a single value like:
    @ERROR_MONITOR = @ERROR_MONITOR + @@ERROR
    ...which will result in a value greater than zero if any errors occured. Or you can store the results of each check separately (@UPDATETABLE1ERROR, @UPDATETABLE2ERROR..etc).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    So is this correct. Check the where clause in the ELSE part and the statement after Update Table 7.
    -------------
    Update table1
    SELECT @ERRORNUM = @ERRORNUM + @@ERROR
    Update table2
    SELECT @ERRORNUM = @ERRORNUM + @@ERROR
    .
    .
    .
    Update table7
    SELECT @ERRORNUM = @ERRORNUM + @@ERROR

    IF @ERRORNUM = 0
    SELECT @RETURNVALUE = 0
    ELSE
    BEGIN
    SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
    WHERE error = @@ERROR (OR is this @ERRORNUM)
    RAISERROR (@ERRORMESSAGETXT, 16, 1)
    SELECT @RETURNVALUE = 1
    END
    RETURN @RETURNVALUE

    Let me know.


    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    @ERRORNUM holds the sum of all the error message IDs, so it can be used to determine whether any error occured but if more than one error occured then it is useless for looking up the error description.

    "SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @@ERROR "
    ...will, again, only give you the error message for the last error to occur.

    If you want to be able to handle multiple errors, create a varchar variable and append any error text to it after each critical step.

    Depending on your process, you may just want to exit the procedure after the first error...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Don't call your procedure "Update". And I don't think you could actually compile what you posted.

    And if you are really interested in capturing all the errors while continuing to do your updates, then you'll need a @temptable to acquire a value of @@error on each update. At the end you would parse a string of all errors by joining this table with sysmessages and scrolling through it while concatenating [description] into a varchar variable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •