Procedure for obtaining the primary key value of a new record (next record) in a table

If the primary key of the database table is not the automatically generated ID column in the project
In many tables, you need to obtain the primary key value of the new record. Each time you connect to the database and calculate the value,
So I wrote a stored procedure that can adapt to different tables, and can define the primary key prefix and default length set quoted_identifier on
Go
Set ansi_nulls on
Go

/*
Obtain the new primary key value in the table.
Call method:
Declare @ newid varchar (20)
Exec getnewid 'customer', 'custid', 'C', 4, @ newid out
Select @ newid
Input parameters:
@ Tablename: Table Name
@ Keyname primary key name
@ Prefix primary key prefix
@ Length the default length when no data exists in the table
Output parameters:
@ Newid: return the new primary key value
*/

Alter procedure getnewid
(
@ Tablename nvarchar (100 ),
@ Key name nvarchar (100 ),
@ Prefix nvarchar (10 ),
@ Length int,
@ Newid nvarchar (20) Out
)
As
Begin
Set nocount on
Declare @ maxid varchar (20)
Declare @ fill nvarchar (20)
-- Declare @ newid varchar (20)
Declare @ SQL nvarchar (1000)

If (@ prefix is null ))
Set @ prefix =''

Set @ fill = '000000'
Set @ SQL = n' select @ maxid = isnull (rtrim (ltrim (max ('+ @ keyname +'), ''' + Left (@ prefix + @ fill, @ length) + ''') from ['+ @ tablename +']'
Exec sp_executesql @ SQL, n' @ maxid varchar (20) out', @ maxid out
 
-- Print 'out: '+ @ maxid
Set @ newid = @ prefix + right (@ fill + Cast (right (@ maxid, Len (@ maxid)-len (@ prefix) + 1) as nvarchar ), len (@ maxid)-len (@ prefix ))
-- Select @ newid
Set nocount off
End

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go