@@identity

  • Thread starter Thread starter Bill Gower
  • Start date Start date
B

Bill Gower

I am using a ExecuteNonQuery() to insert a record into a SQL Server
database. How do I retrieve the identity value of the row just inserted?

Bill
 
Bill,

With SQL Server, Scope_Identity is more reliable than @@Identity.

One technique is to batch a Select Scope_Identity() statement with the
Insert statement. For a command object named cmd:

cmd.CommandText = "Insert Into ... ; Select Scope_Identity()"

Then, instead of using ExecuteNonQuery, use ExecuteScalar. To retrieve the
identity value into an ID variable:

ID = cmd.ExecuteScalar

Kerry Moorman
 
Kerry said:
Bill,

With SQL Server, Scope_Identity is more reliable than @@Identity.

One technique is to batch a Select Scope_Identity() statement with the
Insert statement. For a command object named cmd:

cmd.CommandText = "Insert Into ... ; Select Scope_Identity()"

Then, instead of using ExecuteNonQuery, use ExecuteScalar. To retrieve the
identity value into an ID variable:

ID = cmd.ExecuteScalar

Kerry Moorman
Click to expand...
Another alternative is to use a stored procedure to do the insert, and
in that procedure have an output parameter that will populate with the
new identity value.
 
Kerry is right on the money. It works well with stored procedures too. In the
example I give below, the stored procedure returns the new identity value.
However the example will retrieve whatever return value is coded into the
stored procedure, so it's utility isn't just for new identies.

cmd.CommandText = ""DECLARE @RETURN int; EXEC @RETURN = CustInsert 'John
Smith', '3/15/1959'; SELECT @RETURN [RETURN]"

It's actually a generic wrapper that looks like -

"DECLARE @RETURN int; EXEC @RETURN = " & s & "; SELECT @RETURN [RETURN]"

where s is the procedure with parameters built outside the function.

If you are writing tons of DB code, you don't want to have to deal with
parameter objects, since they don't offer any performance advantage anyway.
 
Cor said:
Rad,

Why should it be a sproc and can it not dynamicly?

Cor
Click to expand...

Cor,

Doesn't have to be a sproc ... I was just floating another alternative
 
Back
Top