The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, November 29, 2021 Login
Public

Stored procedure can not return BIGINT 11/2/2021 2:02:07 PM
SQL Server stored procedures can not return a big integer.

Table has identity value with primary key as big integer.

Stored procedure is running Return Scope_Identity() which works until the identity values exceeds the value of the implicit integer conversion, 2,147,483,646.

This is documented, but rarely run into.

The correct best practice is too use an output parameter for the stored procedure.
This requires changing the procedure and calling code, which can be difficult if this happens in production environment during a busy period.

The temporary solution is too reset the identity value to a negative values, as the range of an integer, -2,147,483,646 to +2,147,483,646.

Watch when you do this, as checkident('tablename') will reset an identity value, so be sure to use noreseed as part of the command.

For monitoring purposes:

dbcc checkident ([table name],reseed,-2147483646)
select max([column identity]),min([column identity]) from [table name] with (nolock)
select count(*) from [table name] with (nolock) where [column identity] < 0


 

and some discussion on the work-around, which is too return the value as an output parameter.

 

https://stackoverflow.com/questions/25915653/return-bigint-in-a-stored-procedure




Blog Home