You should not be inserting into All three tables. You should be inserting data into one, the table named "Bio". The other two tables are list tables. Those tables would very rarely change once loaded.

With the code that you have shown, you would be much better off using MERGE instead of all of that antiquated code.

This code is a waste, since @FirstName is an input parameter that will not allow a NULL. And the front-end should be checking the values for blanks, not the database.

IF @firstname = ''

BEGIN

RAISERROR ('[Error]No first name', 16, 1)

RETURN

END

If that variable from the front-end gets all the way to the database with a blank space, you need to look at the code on the front-end.

The input parameters of

@sex varchar(50),

@status varchar(50)

Should be INT, not varchar(). You need to pass in the RowID, or IDENTITY of the values in the list table, not the actual value. What if you were passing in a country ID? There are just over 240 countries, would you have a huge CASE statement to figure out what ID the country name is? No, you should be passing in the RowID of the list table. That way, if there is a new country name, you do not have to go in all your database code and change an already huge, and very inefficient CASE statement, you simply add the new name to the list table, and the ID will be assigned.

I would never, ever combine my code like that. I have a page to pass in a new row, and a different page to update the existing code. And I have strict security around the page that allows updating. I do check to see if the row exists first, but then do nothing but fall out of the entire sproc returning the ID of the existing RowID. That will let the front-end know that the row already exists. I would never update said row like that.

But I suggest that you look into using MERGE if you want to do both in the same sproc.

Andrew SQLDBA