Return the primary key 1

[AnthonyR]

I am writing a procedure that adds data to a table with an autonumber as a primary key (therefore I do not know what the number will be). How do I return the value of that primary key through the procedure?

 

[MikeJones]

I'm not sure if it's ANSI standard, but if not it should be. Oracle supports a returning clause as part of an insert statement. This means that you can get back any column from the table you just inserted/updated.

i.e.


if we had a table called some_table defined as

PK_Col number
another_col varchar2

and as in your example the PK got automatically done for you

insert into some_table
(another_col)
values ('ABC')
returning pk_col into my_variable;

would put the value used for pk_col into your local variable my_variable.

This may be specific to Oracle, but if you post your RDBMS maybe someone can tell you how to do it for that system.

 

[azhao]

It does not work on MS-SQL 7.0, any other ways to do it? Thanks.

 

[fluteplr]

select @@identity

see further details in the question 'how do I return the last entry'