CODEWITHSUNDEEP
sql-server
Hila
Hila

Reputation: 3

SELECT SCOPE_IDENTITY() Always Returns 1

I'm using this query:

INSERT INTO [LepidoliteDB].[dbo].[EGelLayerWeighingData] ([EGelWeighingDataID], [Amount], [ContainerID], [Portions],
[PortionNumber], [Canceled], [LayerID], [WeighingDone], [InsertDone],
[MeasuresDone], [StartDateAndTime], [EndDateAndTime]) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

SELECT SCOPE_IDENTITY()

But SCOPE_IDENTITY() always returns 1.

What am I missing?

Upvotes: 0

Views: 6916

Answers (5)

gutsy_guy
gutsy_guy

Reputation: 327

Late to the thread, but if someone is using Dapper ORM and they are doing an UPDATE or INSERT a single row and want to know the primary key of that updated record:

var connection = new SqlConnection("ConnectionString");

string sql=@"INSERT INTO [dbo].[Student] ([Name],[Subject])
VALUES (@Name,@Subject)
SET @StudentId=CAST(SCOPE_IDENTITY() as int)";

For the above SQL string, if you execute the SQL connection like:

Result=connection.Execute(sql,new {Name,Subject});

The value of Result will be the number of rows affected, in this case only one since we have inserted only one row. Instead do this:

Result=connection.Query<int>(sql,new {Name,Subject}).Single();

Now Result will have the primary key value of the newly inserted/updated column.

Remember, this answer is only relevant to single row insertion/update

Upvotes: 1

Todd Bell
Todd Bell

Reputation: 21

I had this problem and I must admit it was a dumb mistake, but I thought it may happen to someone else. I was using the Execute command that returns the number of rows affected instead of a Query command.

By the way, I was combining my Insert and SELECT SCOPE_IDENTITY() together in one command, which is why I started with the Execute command. I was still able to combine them, but I used ExecuteScalar instead.

Upvotes: 2

Terry Field
Terry Field

Reputation: 105

I know this is an old thread, but I was having this same issue, and I managed to solve it by going into my XSD and on the query, changing the ExecuteMode of the query from NonQuery to Scalar. This stopped it always returning 1 and instead returning the ID of the previously added row.

It was probably down to me trying to add the SELECT to the INSERT afterwards, rather than doing it all at once and the UI making it correctly for me.

Upvotes: 3

tarzanbappa
tarzanbappa

Reputation: 4958

Best way to write it is ..

RETURN  SCOPE_IDENTITY()

You can also use @@IDENTITY and IDENT_CURRENT for this

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

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.

Therefore RETURN SCOPE_IDENTITY() is the best one.

Upvotes: 1

Johns
Johns

Reputation: 31

Maybe you have an instead-of / for trigger there? what version of MSSQL are you using? Have you tried doing this without a database name? Have you tried this with a begin-end block?

Have you read this article @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

Upvotes: 3

Related Questions