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.