Next Autonumber 2

[rtshort]

What is the correct SQL Syntax to get the next Number of an autonumber field from a table? Identity Column. Rob
Just my $.02.

 

[tlbroadbent]

If you are using SQL 2000 you can use two system functions.

SELECT @NextIdent=
IDENT_CURRENT('TableName') +
IDENT_INCR('TableName')

In SQL 7, you can find the current max value and add the increment value.

SELECT @NextIdent=
Max(IdentCol) + IDENT_INCR('TableName')
FROM TableName

In any case, if the table has a lot of insert activity the value returned by these queries may be obsolete in a few milliseconds. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Terpsfan]

The SQL Server function @@Identity returns the last record number in an Identity column. If you are using a stored procedure to insert records you could just simply return that value to the program.
@NextRecord as int

---Do your insert

SELECT @NextRecord = @@Identity + 1

 

[rtshort]

Thanks guys. You saved the day for me.
Rob
Just my $.02.