Solution to automatic growth of business order number

Problem: in actual business processing, some ticket numbers need to grow automatically, but they cannot be replaced by auto-increment columns.
Eg: p031106001 -- the first p represents the purchase order, 031106 represents the date, and the last three are the sequential number.

/* 1: Create a table structure for testing */
Create Table tablename (Pono char (10), B INT)
Go
/* 2: Create a view to get the current date and prepare for the UDF below */
Create view vgetdate
As
Select getdate () as today
Go
/* 3: Use a custom function to obtain the ticket number (because getdate () cannot be used in the custom function to obtain the current date, the preceding view is required )*/
Create Function getdh ()
Returns char (10)
As
Begin
Declare @ dh1 char (10), @ DH2 char (10)
Select @ dh1 = max (Pono) from tablename
Set @ dh1 = isnull (@ dh1, 'p000000000 ')
Select @ DH2 = left (@ dh1, 1) + right (convert (varchar (8), today, 112), 6) + '001' from vgetdate
If @ dh1 >=@ DH2
Begin
Set @ DH2 = left (@ dh1, 7) + right ('000' + Cast (cast (right (@ dh1, 3) as INT) + 1 as varchar), 3)
End
Return (@ DH2)
End
Go
/* 4: Enter DBO. getdh () */in the default field ()*/
Alter table tablename add constraint df_tablename_1 default (DBO. getdh () for Pono
-/* 5: Test :*/
Insert tablename (B) values (1)
Insert tablename (B) values (2)
Select * From tablename
-- Test Result
Pono B
---------------------
P031115001 1
P031115002 2
Conclusion: Some tips have been applied to this method.
1: use the default field value to realize auto-increment of ticket numbers
2: Use a UDF to obtain the default value of a field.
3: Because undefined functions such as getdate () cannot be used in user-defined functions, views are used to get the current date.