Re: Problem with stored procedure and retrieving inserted identity value

Susanne Klemm (sklemm@xxxxxx) writes:
I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.

There is nothing magic around 700000. Overall, everything you have is 32-bit
integer, so there should be no reason for a clash.

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

In this case, it's better to use scope_identity(), which returns the most
recently generated identity value in the current scope. If PRODUCT has a
trigger which inserts data into a table with another IDENTITY column,
@@identity will return the value for that latter table.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages