Freigeben über

SYSK 124: IDENT_CURRENT Without an Identity Column

  • Artikel

SQL Server’s function IDENT_CURRENT(‘tablename’) returns the last identity value set on a specified table.  While this is very useful, sometimes you need to get the next available primary key on a column that is not an identity field.  In this case,

SELECT IDENT_CURRENT (‘tablename’) will return NULL instead of the value you’re looking for.  If you find yourself in that situation, you should be able to use the code below to get the desired results:

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DECLARE @Command nvarchar(512)

SET @Command = N'SELECT MAX(' + @TableName + '.' + @IdentityColumn +

      N') FROM ' + @TableName + ' WITH (XLOCK)';

BEGIN TRANSACTION

EXEC sp_executesql @Command

COMMIT TRANSACTION

 

Just create a function or stored procedure with the code above, and pass in the table name and the numeric column of your choice.

Comments

  • Anonymous
    May 11, 2006
    This works in SQL 2005 only, doesn't it?
  • Anonymous
    May 11, 2006
    I believe ident_current is available in sql 2000, but not in sql 7
  • Anonymous
    January 08, 2007
    Where is the information stored or maintained in the database, returned by IDENT_CURRENT function? Can we modify the vaue returned by this function?