Tealc
April 30th, 2009, 04:30 PM
Can someone please show me the correct way to INSERT a record into a table? I have seen lots of examples online but they are all different some use error checking some lock the table some do other strange things.
Say I have a simple associate table with a few fields:
First name
Last name
Social security number
ID column
I want to do the following in the stored procedure
1. generate a new Guid and save it in a variable.
2. make sure the ssn does not already exist in the database and make and error if it does.
3. Insert the record this is a multiuser database so do I need locking to make sure the same ssn doesn't get inserted by another process at the exact same time or is this done automatically?
4. Return the newly created guid if the insert was a success.
5. Return a useable error if something went wrong. I know I can use @@error but that returns a simple number and doesn't really tell my program why the insert failed.
The ssn field is a unique index so I guess the insert would just fail if it is already in use.
Should I be trapping these errors in the stored procedure or should I just use a try catch in my c# code and trap the error there providing that error text to the UI?
Say I have a simple associate table with a few fields:
First name
Last name
Social security number
ID column
I want to do the following in the stored procedure
1. generate a new Guid and save it in a variable.
2. make sure the ssn does not already exist in the database and make and error if it does.
3. Insert the record this is a multiuser database so do I need locking to make sure the same ssn doesn't get inserted by another process at the exact same time or is this done automatically?
4. Return the newly created guid if the insert was a success.
5. Return a useable error if something went wrong. I know I can use @@error but that returns a simple number and doesn't really tell my program why the insert failed.
The ssn field is a unique index so I guess the insert would just fail if it is already in use.
Should I be trapping these errors in the stored procedure or should I just use a try catch in my c# code and trap the error there providing that error text to the UI?