Processing method of automatic growth of business number number

problem: In the actual business processing some number numbers need to grow automatically, but can not use the self-added column instead of


eg:p031106001--the first p represents the purchase order, 031106 indicates the date, and the latter three is the serial number.





/* 1: Create a test table structure * *


CREATE TABLE tablename (Pono char (), b int)


Go


/* 2: Create a view that gets the current date and prepare for the following self-literal function * *


CREATE VIEW Vgetdate


as


Select GETDATE () as Today


Go


/* 3: Use a custom function to get a number (because the custom function can not use GETDATE () to get the current date, to use the view above) * * *


Create function GETDH ()


returns char (10)


as


begin


declare @dh1 char (a), @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 ("+" + cast (CAST (Right (@dh1, 3) as int) +1 as varchar), 3


End


return (@dh2)


End


Go


/* 4: Fill in the default value of the field DBO.GETDH () * * *


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 results


Pono b


---------- -----------


P031115001 1


P031115002 2


Summary: This method is applied to some small techniques


1: Using the default value of the field to achieve the number of


2: Use a custom function to get the default value of a field


3: You can use a view to get the current date because you cannot use an indeterminate function such as getdate () in a custom function