returning an identity value when doing a single insert
I have a table with a field which is an identity column. Its the unique identifier of the record.
When a record is inserted via a stored proc, I want to return whatever that new id is to the calling proc.
Is there a function to do this?
Note: This stored proc could be called by many parallel jobs so potentially could be called x2 at the same time. Select Max(column) therefore can potentially get it wrong.. Thats why I was wondering if there was a returning function is something...
Re: returning an identity value when doing a single insert
The feature you are looking for is supported in latest JDBC driver APIs (yeah, it doesn't help you :-) )
Relying on the identity column values to reflect the chronological order in which records were inserted into the table is generally not a good idea. (ie the MAX() stuff might give surprises). especially if you have bulk inserts going on ...
So if you have only single record inserts (which I presume is the case from your description)
you can have a transaction defined in your SP
BT; LOCKING TABLE mytable FOR WRITE INSERT the record here ... SELECT the max(id) here ET;
The explicit WRITE lock will save you any dead lock situation.
Also create a value ordered NUSI on the id column, that will make retrieving the max(id) faster ...
Or may be you need not have it to be an identity column (that would have been my choice), you can keep it as a normal integer column and create an NUSI as before, and do this in your SP
BT; LOCKING TABLE mytable FOR WRITE SELECT COALESCE(MAX(ID), 0)+1 INTO :ID FROM mytable; INSERT INTO mytable VALUES(:ID, ...... ); ET;