Getting Identity when inserting a record from Stored Procedure....

I have a stored procedure that inserts a record into a table.
I need to get the Identity seed or Key that was just created by this
insert. Can I do this inside the stored procedure, returning the just
created key?
Here is the stored procedure...
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1
AS
INSERT INTO a_TixDetail
(SvcTixKey, Date, [Performed by], [Detail Summary],
External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
RETURN
TixKey is a field in the table that is the Primary key. It will be
genereated when the Insert is executed. How can I get this returned from
this procedure?
Thanks,
RogIn SQL Server 2000 use SCOPE_IDENTITY() to return the inserted IDENTITY
value. In SQL Server 7.0 use @.@.IDENTITY.
David Portas
SQL Server MVP
--|||Roger,
Create an output parameter and get the value from the function
SCOPE_IDENTITY().
Example:
ALTER PROCEDURE R_InsertTicketDetail
@.SvcTixKey int,
@.OpenDate datetime,
@.PerformedBy nvarchar(50),
@.Detail ntext,
@.External bit = -1,
@.new_id int output
AS
set nocount on
INSERT INTO a_TixDetail (SvcTixKey, Date, [Performed by], [Detail
Summary],External)
VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
set @.new_id = scope_identity()
RETURN
go
declare @.i int
declare @.rv int
exec @.rv = R_InsertTicketDetail ..., @.i output
print @.rv
print @.i
go
AMB
"Roger" wrote:

> I have a stored procedure that inserts a record into a table.
> I need to get the Identity seed or Key that was just created by this
> insert. Can I do this inside the stored procedure, returning the just
> created key?
> Here is the stored procedure...
> ALTER PROCEDURE R_InsertTicketDetail
> @.SvcTixKey int,
> @.OpenDate datetime,
> @.PerformedBy nvarchar(50),
> @.Detail ntext,
> @.External bit = -1
> AS
> INSERT INTO a_TixDetail
> (SvcTixKey, Date, [Performed by], [Detail Summary],
> External)
> VALUES (@.SvcTixKey,@.OpenDate,@.PerformedBy,@.Deta
il,@.External)
> RETURN
>
> TixKey is a field in the table that is the Primary key. It will be
> genereated when the Insert is executed. How can I get this returned from
> this procedure?
> Thanks,
> Rog
>
>