You can write following code to generate ID from any Table through stored procedure. Here Generate_Id is parameter to pass Id field name. You may have different field name for your table so this stored procedure is common for all tables. This stored procedure may be very helpful for you.
This is a Common code for all table just call this stored procedure and pass table Name, Criteria and Primary key column Name as parameter.
This is a Common code for all table just call this stored procedure and pass table Name, Criteria and Primary key column Name as parameter.
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
Create PROCEDURE [dbo].[Generate_ID]
@TablePK CHAR(50),
@Table CHAR(30),
@Criteria VARCHAR(300)=''
AS
BEGIN
IF @Criteria <> ''
BEGIN
EXEC('SELECT MAX(convert(int,RIGHT('+@TablePK+',5)))AS ID FROM '+@Table+' WHERE '+@Criteria+'')
END
ELSE
BEGIN
EXEC('SELECT MAX(convert(int,RIGHT('+@TablePK+',5)))AS ID FROM '+@Table+'')
END
END
RETURN