How to get next ID for the table - SQL Server
Usually we need to show the next ID on the new entry form, like Sale Order , Purchase Order..etc.
There are different methods for achieve this as far as I know:
1. Create a function or stored procedure to to query the max ID for the table that we want to know the last ID generated and add 1 to the ID.
For example :
Select IsNull(Max(PurchaseOrderId),0) + 1 from PurchaseOrder
But this method will only work only when we have atleast one record in the table, other wise the Max function will return NULL, and it will be converted to zero as we have used ISNULL function and the ID which we will get will be 1, and that could only be correct if you have not inserted any record in the table, otherwise if you have entered a record in the table and deleted that record, its ID will be starting from the last ID generated.
2. Another method, that is usually used is that we check if the query / resultant of the above query is NULL, we reseed the table identity column to 1.
declare @nextId as int
set @nextId = (Select Max(PurchaseOrderId) from PurchaseOrder)
IF @nextId IS NULL
DBCC CHECKIDENT ('PurchaseOrder', RESEED, 1)
Select IsNull(@nextId,0) +1 as NextIdentity
which seems to be quite better way to achieve the next ID for a table.
But the best way I have found yet is :
3. There is a built-infunction in SQL SERVER 2005 AND LATER, and that is :
IDENT_CURRENT( 'table_name' )
Ident_Current() function gives you the last ID which was generated for the particular table.
For example :
Select IsNull(IDENT_CURRENT( 'PurchaseOrder' ) ,0) + 1 as NextIdentity
Now its upto you that how you use this function, I have created a general stored procedure, which accepts an argument for the table name, and passed that arguemnt /variable to the Ident_Current() function, and now I don't have to create seperate funtion / stored procedure for each table.
CREATE PROCEDURE [dbo].[GetCurrentIdentityForTable]
SELECT IsNull(IDENT_CURRENT( @TableName ),0) + 1 as CurrentIdentity
That was all!! If you can suggest more better method to achieve this or any other alternative, please feel free to share that :-)..