problem with SP to return last @@Identity

I need to retun the last inserted Identity value. - The SP below has a synta
x
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"Rich" <Rich@.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.|||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?
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> 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|||create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Select @.e = @.@.Identity
return
go
--This seems to work
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> 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|||Thanks. From what I understand Scope_Identity works within a specified scop
e
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 row
s
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?
?
"Raymond D'Anjou" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
> Set @.e = @.@.Identity
> or
> Select @.e = @.@.Identity
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>|||"Rich" <Rich@.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.sql