Error Handling

[Work23]

Hello. I am intereted in inserting an error handling function with some SQL code I created. The code is below. I would like to be able to send some error output(should an error occur when the program is ran) to an error table I would create. I would need last EventId ran correctly along with the other fields attached to that EventId. Please let me know if you need further clarification. The code is below. Thank you so much!



TRUNCATE TABLE TABLE FINALTABLEMERGE
INSERT INTO TABLE FINALTABLEMERGE
SELECT TFINAL_EVENTID
, TFINAL_EVENTNAME
, TFINAL_SESIONID
, TFINAL_TIMESTAMP
, TFINAL_EVENTNAME
, TFINAL_USERID
, TFINAL_SOLDTO
, TFINAL_LOCALE
FROM TABLE LOGINFINAL
UNION
SELECT TEVENTID
, TEVENTNAME
, TSESSIONID
, TTIMESTAMP
, TEVENTVALUE
, ''
, ''
, ''

FROM TABLE PAGEVIEWEDFINAL
UNION
SELECT TEVENTID
, TEVENTNAME
, TSESSIONID
, TTIMESTAMP
, TEVENTNAME
, ''
, ''
, ''
FROM TABLE LOGOFFINAL
ORDER BY TFINAL_SESIONID;

 

[Catadmin]

What you seem to be asking for is fairly simple, though how you insert in into your code is up to you. Remember that error codes change everytime you do another action in T-SQL. You'll want to declare an ErrorCode variable, then literally Set that variable as soon as you're done with the action in question.

Something like:

Declare @ErrNumTruncate int,
@ErrNumInsert int,
@ErrNumSelect int;

TRUNCATE TABLE TABLE FINALTABLEMERGE;
Set @ErrNumTruncate = @@Error;
INSERT INTO TABLE FINALTABLEMERGE;
Set @ErrNumInsert = @@Error;
SELECT TFINAL_EVENTID
    ...  FROM TABLE LOGINFINAL
UNION
SELECT ...  FROM TABLE PAGEVIEWEDFINAL
UNION
SELECT ...  FROM TABLE LOGOFFINAL
ORDER BY TFINAL_SESIONID;
Set @ErrNumSelect = @@Error;

 If @errnumTruncate = 0 
  Begin 
   <do your error handling here>
  End 
  Else
        Break;

You'll want to fiddle with how exactly you handle your errors. You might want to even do if the variable is not equal to zero to do something rather than if it is equal to zero.

Notice how I declared three different @@ErrNums in this code. This was deliberate because I wasn't sure where you wanted to do your error handling (at the end of all your code or after each statement). If you do your handling at the end, like I did, and you use the same variable for all three sections, it will get overwritten. Hence the need for three different variables and three different (though I didn't write them) error handling events at the end.

Look at Books Online using @@Error to get more details.

Hope this helps you somewhat. If you're looking for something more specific, you'll need to post what errors you're trying to catch at what point in your code and what you want done at that point.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[Work23]

Thanks for the idea. Will be sure to post soon with specifics. Thanks again.

 

[Work23]

Using the code below, I need to capture the last or 'Max' Event ID in the FinalTableMerge table and also in the Stats table to see if they are equal. If they are equal, then the program should do the rest of the code I would have below it. If not, it should print an error message saying Event Id's do not match. I looked at the Raise error commands on the BOL and am really confused on how to set this up. Please let me know if you have any ideas. Thanks so much. Take care.



TRUNCATE TABLE FINALTABLEMERGE
INSERT INTO TABLE FINALTABLEMERGE
SELECT TFINAL_EVENTID
, TFINAL_EVENTNAME
, TFINAL_SESIONID
, TFINAL_TIMESTAMP
, TFINAL_EVENTNAME
, TFINAL_USERID
, TFINAL_SOLDTO
, TFINAL_LOCALE
FROM TABLE LOGINFINAL
UNION
SELECT TEVENTID
, TEVENTNAME
, TSESSIONID
, TTIMESTAMP
, TEVENTVALUE
, ''
, ''
, ''

FROM TABLE PAGEVIEWEDFINAL
ORDER BY TFINAL_SESIONID;

/*INSERT FINALTABLEMERGE INTO STATS TO APPEND DAILY EVENTS*/
TRUNCATE TABLE DBO.STATS
INSERT INTO DBO.STATS
SELECT *
FROM DBO.FINALTABLEMERGE
/*END INSERT*/