Check Referential Ingerity or Catch Error

Hi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

CyherusHi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @.err = @.@.ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

Cyherus

Any error above 16 won't be caught. You need to catch those at the client or app level. If you're having these types of RI issues though, you have an app design issue. The PK should already be known by the app when you go to insert foreign key records. On the insert of a new PK, it should either be auto or a true natural key. In this case, there is no chance of error.|||Many thanks.. now things are much clearer..

I'm reading these data from a text file and foreign keys on these records are not checked against the primary tables in my DB, thus the need to handle this.

I dun quite get you when you mention about inserting new PKs, what do you mean by auto or true natural key??