Thanks Sergey. Thats exactly what I need.

I'm having trouble calling it from VFP:

Heres the proc:

CREATE PROCEDURE gen_new_id
 @key_name	varchar(50),
 @newID int
AS

SET NOCOUNT ON

UPDATE gen_new_id
SET 
 @newID = id_value = id_value + 1
WHERE
 key_name = @key_name

IF @@ERROR <> 0
 RETURN 1
ELSE
 RETURN 0

and my code. I don't think I'm doing this right.

sSqlConnStr = "DRIVER=SQL Server;SERVER=dserver3;database=newmegadb;uid=sa;password="

iConnHandle = SQLSTRINGCONNECT(sSqlConnStr)

IF iConnHandle > 0

	cKey = "my_key"

	nResult = SQLEXEC(iConnHandle, "get_new_id @cKey")

	? nResult

	SQLDISCONNECT(iConnHandle)
	
ELSE
	? "Not connected"
ENDIF

>See Re: Record ID generator question Thread #605839 Message #611104
>
>>How do I convert this VFP code to a SQL stored procedure:
>>
>>

>>PROCEDURE GetNextId(cKeyName)
>>
>>LOCAL nRetVal
>>nRetVal = 0
>>
>>SELECT 0
>>USE SysKeys
>>
>>LOCATE FOR KeyName = cKeyName
>>
>>IF FOUND()
>>  nRetVal = SysKeys.Id + 1
>>  REPLACE IN SysKeys Id WITH nRetVal NEXT 1
>>ENDIF
>>
>>RETURN nRetVal
>>