how to retrieve the value of a primary key

[olorunfunmi]

hi there,
pls how can i retrieve the value of a primary key in a table using a stored procedure.
i tried sp_pkeys but that just displays the column name of the primary key and not the value
thanks

[Purple]

Hi olorunfunmi,

are you trying to get the value of the primary key for a row you have just inserted and the field has identity set on ?

if yes try:

Select @@identity as variable_name

within the stored procedure

Regards Purple

[DKelley]

Isn't SCOPE_IDENTITY( ) the better function to use? @@Identity just returns the last primary key which may have been in a different scope.

That is, if a another user called a stored procedure that ALSO created a new record my understanding is that the newest primary key would be returned - even it if was created for a different user in another scoped instance of that same stored procedure.

[Purple]

Hi DKelley,

It is an interesting question you raise and I guess the answer is yes and no :)

@@IDENTITY returns the last identity entered on the table by your current session (this is limited to your session only, you will not get identities entered by other users). While @@IDENTITY is limited to the current session, it's not limited to the current scope. therefore, if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SCOPE_IDENTITY( ), like @@IDENTITY, returns the last identity value created in the current session, but it additionally limits it to your current scope as well. In other words, it returns the last identity value you explicitly created, rather than any identity created by a trigger / user defined function.

There appears to be alot of misconception around @@IDENTITY - I guess as always the devil is in the detail..

Regards Purple