Automatic SQL Server database forms

Reference http://bbs.chinaunix.net/thread-3838790-1-1.html
-- BelowCodeThe length of the generated serial number is 12. The first six digits are the date information. The format is yymmdd, And the last six digits are the serial numbers.
-- Create a view for the current date, because the uncertain function cannot be used in user-defined functions, and getdate is an uncertain function. You must create a ---- view to obtain the value of getdate.
Create view v_getdate
As
Select dt = convert (char (6), getdate (), 12)
Go

-- Get the function with the new number
Create Function f_nextbh ()
Returns char (12)
As
Begin
Declare @ DT char (6)
Select @ dt = dt from v_getdate
Return (
Select @ DT + right (1000001 + isnull (right (max (BH), 6), 0), 6)
From TB with (xlock, paglock)
Where BH like @ DT + \ '% \')
End
Go

-- Apply a function to a table
Create Table Tb (
BH char (12) primary key default DBO. f_nextbh (),
Col INT)

-- Insert data
Insert Tb (COL) values (1)
Insert Tb (COL) values (2)
Insert Tb (COL) values (3)
Delete TB where Col = 3
Insert Tb (COL) values (4)
Insert Tb (BH, col) values (DBO. f_nextbh (), 14)

-- Display Results
Select * from TB
/* -- Result
BH col
------------------------------
050405000001 1
050405000002 2
050405000003 4
050405000004 14
--*/
If the view is in the format of 8-bit date yyyymmdd
Create view v_getdate
As
Select dt = convert (char (8), getdate (), 112)
Go
You can modify the view if you want to add another character sign to the money area.
Create view DBO. v_date
As
Select 'ff '+ convert (char (8), getdate (), 112) as dt
'Ff 'can be replaced with any character you want. Note that the number of characters in the following function must also be increased.
The complete example is as follows.

Create view DBO. v_date
as
select 'ff '+ convert (char (8), getdate (), 112) as dt
go
// create a user-defined function in the SQL Server database
Create Function f_idnumber ()
Returns char (16)
as
begin
declare @ DT char (10)
select @ dt = dt from v_date
return (
select @ DT + right (1000001 + isnull (right (max (Num), 6 ), 0), 6)
from test_tb1 with (xlock, paglock)
where num like @ DT + '% ')
end
go
-- // use the UDF in the test table
Create Table test_tb1 (
num char (16) primary key default DBO. f_idnumber (),
itype int
)

insert test_tb1 (itype) values (2)
insert test_tb1 (itype) values (3)
insert test_tb1 (itype) values (4)
insert test_tb1 (Num, itype) values (DBO. f_idnumber (), 12)
select * From test_tb1
-- The result is as follows
-- ff201212310401 1
-- ff201212310402 2
-- ff201212310403 3
-- ff20121231000004 4
-- ff201212310405 12