get inserted id of identity column

Hi,

a situation I am always confronted with is: insert into a table, get this damn auto generated value to work with it. I don't know any database independent solution (except of using the max id inserted, what can be ok in some cases, but can fail too).

In sql server I found a solution for this problem at technet: http://technet.microsoft.com/de-de/library/ms190315.aspx ... this page describes a stored function which returns exactly the value we need (some kind of magic happens here).

The (filtered) example of technet:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE TZ ( Z_id int IDENTITY(1,1)PRIMARY KEY, Z_name varchar(20) NOT NULL); INSERT TZ VALUES ('Lisa'),('Mike'),('Carla'); -- inserts 1,2,3 INSERT TZ VALUES ('Rosalie'); SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]; -- returns 4

Kind regards,
Daniel