Hi Jeff,
It seems to me that I am confusing you guys with our requirements.
If so, I sincerely apologize.
I also thank you guys for your patience.
Quite frankly, I don't even think what I am asking is possible. I am just hoping for a better approach.
So, please allow me to try explaning it again.
As stated previously, we have 10 tables, including the Employees table.
All these tables are related by EmployeeID, PK , identity seed for Employees table.
There is another important table worth mentioning here, dateDetails table.
This table contains dates stored when an employee submits a record.
So, as it is now, our management mandates employees to take an avidavit that lists their sources of incomes other than their current employment.
Even if your source of income is your current employment, you are still required to fill this form.
If the employee has a spouse, s/he must also disclose spouses income. (Stupid if you ask me because it is not their business where spouse's income comes from but it is what it is).
This avidavit must be taken every year.
That's why we have dateDetails table to keep track of the year(s) an employee has completed this form.
So, in our query, we join dateDetails table with Employees table on EmployeeID and current year if we want to know the year form is completed.
So far clear, I hope.
Where it gets dicey for us is that after each form submission, we grab the last inserted key of the Employees table.
Using this key, we insert records into the rest of the tables including the dateDetails table.
So, asume that employee Joe Blo completed this form in 2018, when he loads the app, enters a unique employee number (diferent from EmployeeID), the app is able to recognize that Joe Blo completed this form in 2018 and then loads his personal information like name, title, email, phone and that unique ID.
Joe Blo then goes ahead to complete the rest of form vis-a-vis, if he has another source of income or same as last year, if he was single last year and got married this year, if the wife's income source has changed, etc.
To do this, a new EmployeeID needs to be generated.
If we do an UPDATE statement like we did on the stored proc posted here, no new employeeID will be generated since it is just an update.
When this happens, only Employees table gets updated. However, new records need to be inserted the other tables and this generates an error that "DBNULL conversion to integer is invalid"
I believe the reason for the error is that the app is looking for a new employeeID to be inserted into the tables but is not finding any.
That led to my question as to whether the existing identity seed in Employees table can be incremented so that if the existing id is say 2365, then a new one, 2366 will be generated and then used for inserting into other tables.
Otherwise, we will just have to insert employees info all over again into Employees table just to grab the latest inserted ID.
IT appears that the db design is flawed because inserting same information over and over again into Employees table creates anomalies even though we can obtain the most recently inserted records by joining Employees table with dateDetails table to obtain current year.
So, I guess my question is that if it is not possible to increment identity seed, any ideas how to redesign the table so only Empoyee info gets inserted into Employees table just once and Employee IDs can be inserted into another table once a new record is created?
This way, we join that table with Employees table and the rest of the tables to get the most recently inserted records to display the employees.
I hope this a bit clearer?