Dynamic auto generate number in sql server

When you insert data into a table, the server can generate that value for you - do not try it yourself, as you might face concurrency problems (two users adding data with the same new id value).
I the table definition, use an integer with the "identity" property switched on ("Is Identity", "Yes").
When you need to know that value on your clent after inserting into the database, do a SELECT @@ID.

use below Query,

select isnull(max(AutoIncrFldNm),0) + 1 from tblnm

In store-procedure if you are passing tablename & fieldname dynamically
then,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sp_ReturnNextAutoId
	@tblNm as varchar(50),
    @fldNm as varchar(50)
AS
BEGIN
	DECLARE @execquery AS NVARCHAR(MAX)
	SET @execquery = 'select isnull(max(' + QUOTENAME(@fldNm) + '),0) + 1 from ' + QUOTENAME(@tblNm)
	execute(@execquery)
END
GO

--tocheck sp
--exec Sp_ReturnNextAutoId 'tbl1','fld1'

Happy Coding!
:)

Create proc sp_increment
(@TableName NVARCHAR(100),
@ColumnName NVARCHAR(100)
) AS
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT Isnull(MAX(' + @ColumnName + '),0)+1 FROM ' + @TableName
print @sql
EXECUTE sp_executesql  @sql

When i have no data in my table, it will return me 1

If i have exisitng data, then it will return me max+1

sp_increment 'tbl_user' ,'userid'