-=============================================
- Create an author: XCZ
- the Create date: & lt; The 2020-12-24 & gt;
- Description: & lt; Automatically generate water code, prefix + water + date code, such as PG - 20210320-0001 & gt;

-=============================================
The ALTER proc [dbo] [CreateSerial]
(@ tbname nvarchar (10), the number - the need to generate the table name
@ rq datetime, - date column values, the second part of the generated code

@ returnValue nvarchar (50) output)
As
Declare @ pre nvarchar (10) - serial number prefix
Declare @ mid nvarchar (10) - serial number date format
Declare @ sufwide int - serial number suffix length
Declare @ I int - recycled
Declare @ suffix nvarchar (10) - serial number suffix
Declare @ date nvarchar (10) - date format
Declare @ maxNo nvarchar (50), the current biggest serial number


- by ID to obtain the corresponding serial number format
The begin
Select @ pre=pre, @ mid=DateRule, @ sufwide=sufwide
The from serial_rul - table of
numbering rulesWhere @ tbname=tbname
- according to transfer the format information of (date) (month) (year) on the day of the date information
If @ mid='yyyyMMdd'
The begin
Select @ date=the Convert (varChar (10), @ rq, 112)
End
If @ mid='yyMMdd'
The begin
Select @ date=right (Convert (varChar (10), @ rq, 112), 6)
End
- for current table in the serial number of the largest
Declare @ tems1 nvarchar (Max), @ tems2 nvarchar (Max), @ tems3 nvarchar (Max)
The set @ tems1='select';
The set @ tems2='=Max (number) from';
The set @ tems3='where Numbers like;

Exec (@ @ maxNo tems1 + + @ @ tbname tems2 + + + @ @ tems3 pre + '-' + @ date + '%')

Haven't start produce number is 1 - day number
If (ISNULL (@ maxNo, ' ')='
The begin
Select @ suffix=the convert (varchar (10), the convert (int, 1))
The set @ I=1
While @ i<@ sufwide
The begin
The set @ suffix='0' + @ suffix
The set @ I=@ I + 1
End
Set @ returnValue=https://bbs.csdn.net/topics/@pre+ '-' + @ date + '-' + @ suffix
End
The else
- daily number is the largest number of suffix string + 1
The begin
The set @ suffix=the convert (varchar (10), the convert (int, right (@ maxNo, @ sufwide)) + 1)
The set @ I=@ sufwide - len (@ suffix)
While @ i<@ sufwide
The begin
The set @ suffix='0' + @ suffix
The set @ I=@ I + 1
End
Set @ returnValue=https://bbs.csdn.net/topics/@pre+ '-' + @ date + '-' + @ suffix
End
end

CodePudding user response:

Exec (@ @ maxNo tems1 + + @ @ tbname tems2 + + + @ @ tems3 pre + '-' + @ date + '%'), the @ maxNo here as the output variable to return

CodePudding user response:

@ maxNo just find out the current table within process largest number! Eventually joining together is this: the select @ maxNo=Max (number) where the from list A number like 'PG - %'

CodePudding user response:

refer to the second floor x18573691 response:

@ maxNo just find out the current table within process of largest number! Eventually joining together is this: the select @ maxNo=Max (number) where the from list A number like 'PG - %

The debugging, @ maxNo have get to the data

https://blog.csdn.net/conganguo/article/details/47213631, look at this, similar to your needs