In SQL Server 2000, with an autonumber ID field, how do you get the value that will be the ID of the next record (without setting it)?
Printable View
In SQL Server 2000, with an autonumber ID field, how do you get the value that will be the ID of the next record (without setting it)?
SELECT TOP(autonumberidfield) FROM tablename
and add 1 to it!
But keep in mind, if another user INSERTs before you do, you won't get the right number.
Use the IDENT_CURRENT() function:
IDENT_CURRENT returns the last identity value added to a table. Incrementing that number by one should return the next number.Code:Select IDENT_CURRENT('table_name') + 1
Ok, thanks guys... and that won't be a problem... it's more for display than anything else. They actual update will grab the true value and it isnt' necessary for them to know the value anyways.Quote:
Originally posted by mendhak
But keep in mind, if another user INSERTs before you do, you won't get the right number.
Ok, neither of those work and I just realized that I'm still using my test server (SQL Server 7). I haven't migrated to the SQL 2000 Server yet.
Anyone know how to do it with Server7?
try
SELECT ISNULL(MAX(autonumberidfield),0)+1 AS NextId FROM tablename
Nope, that doesn't work either. That grabs the next one in sequence, but there have been several deleted and thus the number it should be grabbing should be higher than that. (I'm getting 737 and it should be more like 750 something)
*bump*
Not near a 100% solution, since I've no v7 to test or validate this on. But I hope it helps or
gives you an idea or two.Ah, the ways I love to not miss pre-2K days!Code:-- As vb_dba says in mssql2k+: SELECT IDENT_CURRENT('tablename') + IDENT_INCR('tablename')
SELECT name
FROM sysobjects
WHERE type = 'U' AND IDENT_SEED(name) IS NOT NULL
SELECT
IDENT_SEED(OBJECT_NAME(id)) AS Seed
, IDENT_INCR(OBJECT_NAME(id)) AS Increment
, OBJECT_NAME(id)
, autoval
FROM syscolumns
WHERE (status & 128) = 128
-- see MSDN Q104829 for sp_hexadecimal
declare @binvalue varbinary(256), @hexvalue varchar(256)
SET @binvalue = (SELECT autoval FROM syscolumns WHERE name = 'IdentFieldName')
EXEC sp_hexadecimal @binvalue, @hexvalue OUTPUT
--print @hexvalue
-- curIdent___ Step_______ Seed_______
-- 0x FE 08 00 00 01 00 00 00 01 00 00 00 82
DECLARE @hexstr varchar(10), @curIdent int , @NextStep int , @cmdstr nvarchar( 255 )
SET @hexstr = '0x' + substring( @hexvalue, 9, 2 ) + substring( @hexvalue, 7, 2 ) + substring( @hexvalue, 5, 2 ) + substring( @hexvalue, 3, 2 )
SET @cmdstr = N'SELECT @curIdent = convert( int , ' + @hexstr + ' )'
EXEC sp_ExecuteSql @cmdstr, N'@curIdent int out', @curIdent out
--SELECT @curIdent
SET @hexstr = '0x' + substring( @hexvalue, 17, 2 ) + substring( @hexvalue, 15, 2 ) + substring( @hexvalue, 13, 2 ) + substring( @hexvalue, 11, 2 )
SET @cmdstr = N'SELECT @NextStep = convert( int , ' + @hexstr + ' )'
EXEC sp_ExecuteSql @cmdstr, N'@NextStep int out', @NextStep out
SELECT @curIdent + @NextStep NextIdentVal
;-)
wow... it's not THAT important. Thanks anyways.
Try looking at @@identity in books online, I'm sure that has something to do with it from memory.