problem with SP to return last @@Identity

2 Feb 2006 5:45 PM

Rich

I need to retun the last inserted Identity value. - The SP below has a syntax
error on the Set line. How can I fix it?

create procedure stp_GetIdentity
@e int output
as
SET NOCOUNT ON

Set @e = Select @@Identity
return
go

Thanks,

Rich

2 Feb 2006 6:10 PM

Raymond D'Anjou

Show quote

"Rich" <R***@discussions.microsoft.com> wrote in message
news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@microsoft.com...
>I need to retun the last inserted Identity value. - The SP below has a
>syntax
> error on the Set line. How can I fix it?
>
> create procedure stp_GetIdentity
> @e int output
> as
> SET NOCOUNT ON
>
> Set @e = Select @@Identity
> return
> go
>
> Thanks,
>
> Rich

Set @e = @@Identity
or
Select @e = @@Identity

Option 1 is preferred for a single assignment.
Also look up scope_identity() in BOL.

2 Feb 2006 6:26 PM

Rich

Thanks. From what I understand Scope_Identity works within a specified scope
which I interpret to mean if you insert a row into tbl1 which contains 10
rows in one procedure and also insert a row into tbl2 which contains 700 rows
in another procedure and you only want to return the Identity value in tbl1
you could use Scope_Identity.

May I ask how Scope_Identity would be implemented in my SP to return the
Identity value of the last inserted row into tbl1?

?

Show quote

"Raymond D'Anjou" wrote:

> "Rich" <R***@discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@microsoft.com...
> >I need to retun the last inserted Identity value. - The SP below has a
> >syntax
> > error on the Set line. How can I fix it?
> >
> > create procedure stp_GetIdentity
> > @e int output
> > as
> > SET NOCOUNT ON
> >
> > Set @e = Select @@Identity
> > return
> > go
> >
> > Thanks,
> >
> > Rich
>
> Set @e = @@Identity
> or
> Select @e = @@Identity
>
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>
>

2 Feb 2006 7:08 PM

Raymond D'Anjou

Show quote

"Rich" <R***@discussions.microsoft.com> wrote in message
news:6E159771-9CB3-45ED-95D1-7EF10E72DFFA@microsoft.com...
> Thanks. From what I understand Scope_Identity works within a specified
> scope
> which I interpret to mean if you insert a row into tbl1 which contains 10
> rows in one procedure and also insert a row into tbl2 which contains 700
> rows
> in another procedure and you only want to return the Identity value in
> tbl1
> you could use Scope_Identity.
>
> May I ask how Scope_Identity would be implemented in my SP to return the
> Identity value of the last inserted row into tbl1?
>

set @a = scope_identity()

scope_identity() has another advantage.
If you have a trigger on a table that inserts a row into another table with
an identity column.
@@identity in your stored procedure will return the ID of the last insert,
that is, the one in your trigger.
scope_identity() will return the ID you want.

2 Feb 2006 6:14 PM

Mark Williams

The offending line should be

SELECT @e = @@IDENTITY

however, why create a stored procedure to get @@IDENTITY, when you can just
retrieve its value within a batch using SELECT @@IDENTITY?

--

Show quote

"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syntax
> error on the Set line. How can I fix it?
>
> create procedure stp_GetIdentity
> @e int output
> as
> SET NOCOUNT ON
>
> Set @e = Select @@Identity
> return
> go
>
> Thanks,
>
> Rich

2 Feb 2006 6:15 PM

Rich

create procedure stp_GetIdentity
@e int output
as
SET NOCOUNT ON

Select @e = @@Identity
return
go

--This seems to work

Show quote

"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syntax
> error on the Set line. How can I fix it?
>
> create procedure stp_GetIdentity
> @e int output
> as
> SET NOCOUNT ON
>
> Set @e = Select @@Identity
> return
> go
>
> Thanks,
>
> Rich