How to get the inserted identity field at the same Stored Procedure ? 2

[royc75]

Hello,

I have a table with two columns: One is an identity and one is dummyString.
I would like to create a Stored Procedure which will make an INSERT to this table and than return the identity of the row that was just inserted. How do I do that?
And by the way, is it possible to make an INSERT to a table that has only one identity field?

 

[bborissov]

I don't see any reason to have such a table with one column and that column to be IDENTITY? Why you need it? I can't think any use for it. About your question:

INSERT INTO MyTable (DummyString) VALUES ('More Dummiest')
SELECT @@IDENTITY
-- SELECT SCOPE_IDENTITY() in SQL Server 2005

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Hello Borislav,

Thank you for your response. I need this table in order to manage keys in my application. At the moment I use dummyString as another column for the INSERT, I jest thought it is possible to drop it.
Anyway, regarding your code: suppose multiple users are using this query, Am I exposed to a situation where the @@IDENTITY returned does not belong to the same INSERT ?

 

[bborissov]

Instead of having a table with one column with identity field, why not put identity field in all tables where you must to manage keys? This is only suggestion, I don't know your current DB logic.

Anyway, regarding your code: suppose multiple users are using this query, Am I exposed to a situation where the @@IDENTITY returned does not belong to the same INSERT ?

In that case (you use @@IDENTITY in SP) @@IDENTITY must returns current value of current insert. But If you use SQL 2005 use SCOPE_INDENTIY()this will returns the last IDENTITY of current SCOPE (in that case your SP).

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[unclejimbob]

SQL Server 2000 introduced SCOPE_IDENTITY() and IDENT_CURRENT() as alternatives to @@IDENTITY, so in your case you can use SCOPE_IDENTITY()

 

[bborissov]

2000?
Why I think they are new in SQL Server 2005?
Thak you unclejimbob!
I am happy I learn something today.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[bborissov]

Star for you!

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Thank you both.
So, to conclude what should I use? @@IDENTITY, SCOPE_IDENTITY() or IDENT_CURRENT() ?

 

[bborissov]

SCOPE_IDENTITY() it will returns the last identity wich you are inserted in current SP. That way there is no risk to mess with other inserted value somewhere.
From BOL:

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

10X Borislav, and what is IDENT_CURRENT() ?

 

[bborissov]

BOL:

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.


SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Thank you Borislav I couldn't ask for a clearer explanation.
So, if I understand correctly, the "safest" one to use would be SCOPE_IDENTITY. Am I right?
By the way, what is BOL?

 

[bborissov]

Yes, savest way is SCOPE_IDENTITY() if you use SP.
BOL = Books on-line (SQL Server help )

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[royc75]

By the way, what if I don't use SP but use it as a query from my code? The way I see it it doesn't matter since the SQL Server will synchronize the requests and the SCOPE_IDENTITY() will protect me from getting the wrong number. What do you think?

 

[unclejimbob]

This sounds correct to me.

From 2005 BOL...

SCOPE_IDENTITY()
"Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or _batch_. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or _batch_."

"Batch" defined as:
"A batch is a group of one or more Transact-SQL statements sent at one time from an application to SQL Server 2005 for execution."

I guess the only time a problem would occur would be if the INSERT statement failed in such a way as to cause the current identity for the table to be incremented - the circumstances where this can occur is also in the BOL.

The best way is to actually fire it up and give it a whirl - then you know it will work in your particular environment.

 

[royc75]

You are right, I've already implemented it and until now it seems to be working OK (touch wood... )