>>>Yeah, you definitely can't insert data into a table inside a function.
>>>
>>>Let me ask this - are you simply looking to return an integer that represents the newly-inserted identity value? After reading your posts again, maybe I don't quite understand what the objective is.
>>
>>I have an Employees table, and it has an Employee number field emNumber (int). I cannot make the emNumber field autoinc, because it cannot be used as a unique key (unique keys must be meaningless, but the employee number is not meaning less), so I have another table EmployeeID that has the autoinc field (and nothing else).
>>
>>In my VFP program, when the user saves the new employee record, the program creates a new employee number by simply creating a new record in the EmployeeID table, then it gets the last created autoinc value and uses this to insert into the employees table.
>>
>>When inserting a new employee in the SQL database (not using the VFP program), I need to get a new emNumber from the EmployeeID table. Our clients want to be able to create employees directly in the database, so they can't use the VFP code, but need to do that in SQL command. I could tell them to first create the record in EmployeeID, get the last id number and use that for the new Employees record, but it would be better (at least easier for them) if I can give them a function to do that.
>
>Can you post structure of both tables? But basically, you need a stored procedure to create new Employee record. You will pass the information needed to create the new employee and this stored procedure will do the rest of inserting into the 2 tables you need - e.g. it will hide the complexity of the process.

I think the best solution is to do two statements instead:
Insert into EmpID (emKey) Values (newid())
Insert into Employees (emKey, emNumber) Values (newid(), scope_identity())

I thought it would be easier to do it with a function call, but if it is not a simple solution it would not make sense, so I think it's as good as it gets.