Posted by Erland Sommarskog on 07/11/06 13:28

Susanne Klemm (sklemm@gmx.de) 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@sommarskog.se

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

[Back to original message]