Unfortunately, I could not get this to work in my situation. At first it looked intuitive, but what I have is an IDValues table that keeps track of the auto-inc field and need to increment this during the insert into the Employees table.

To create a workaround I am thinking to create a function, to make it simpler, but I get the error "Only functions and extended procedures can be executed from within a function". I thought it would be easy to create a function with a SQL statement and return the value of the new ID field. My goal is to have a function that I can call in the insert statement like this:

Insert into Employees (emName, emId) VALUES ('Albert', GetNewEmployeeNumber())

The function could look something like this, with the database name as parameter (because that function should be called in a main database and the table is in another database):

CREATE FUNCTION [dbo].[GetNewEmployeeNumber]
	(
	@tnCoID int = 0
	)
	RETURNS int
AS
BEGIN
	
   DECLARE @table table (id int)

    DECLARE @lcSQL nvarchar(max)
    SET @lcSQL  = 'INSERT INTO Payroll' + ltrim(str(@tnCoId)) + '..EmpID (emKey) OUTPUT Inserted.emId INTO @table VALUES (NewID())'
    EXECUTE sp_ExecuteSQL @lcSQL
	
    RETURN (SELECT ID FROM @table)
END
ENDTEXT

>Try this example:
>
>

>create table NameTest (IDValue int identity, Name varchar(50))
>create table IDValues (IDValue int)
>go
>
>
>insert into NameTest  output Inserted.IDValue  into IDValues 
>   values ('Steve Goff'), ('Katy Goff'), ('Kevin Goff')
>
>select * from IDValues
>

>
>Note what's happening here...the OUTPUT statement lets you "tap into" the state of the rows as they are inserted....you can OUTPUT the results of an INSERT and even OUTPUT them into a table.
>
>If you don't use the INTO, and just OUTPUT Inserted.IDValue, it will send the value(s) you inserted directly as a result set.
>
>So this lets you get immediate feedback on what was inserted. Let me know if that helps...
>
>(Note....if you're only ever looking to get one value based on your identity key...you can also use SCOPE_IDENTITY...the nice thing about OUTPUT is that it allows you to bring back other calculated columns from the original INSERT)