Table has two columns: A [number] [nvarchar] (16) NULL, car [date] [date] NULL
Want to be in the [number] column according to the [date] column according to the daily increasing to generate the serial number, format: PG - YYYYMMdd - 0001, the stored procedure code is as follows: when debugging tip: from a string when converting date and/or time, conversion fails, a great god, please put the pulse, how to change!
-=============================================
Author: & lt; * * & gt;
- the Create date: & lt; The 2021-05-14 & gt;
- Description: & lt; Automatically generate water code, prefix + water + date code, such as PG - 20210320-0001 & gt;
- the date by one column, according to the year, month, day, respectively generate water code
- date mode: 20210424
0 (date) (month) (year) e.g. :- 1 year e.g. : 202104
- e.g. 2 years: 2021
-=============================================
The ALTER PROCEDURE [dbo] [GetNoByColname] (
@ prefix varchar (10) - prefix letter
@ mode int - date mode
@ coltext datetime, - the value of the date column to the incoming date type of value, rather than as getdate () to generate Numbers
@ nolen varchar (10) - serial number length
@ listna varchar (20), - water number corresponding to the column, it is recommended that the index
@ tablena varchar (20), - table name
@ runningnum varchar (20) the output) - generated code water
AS
The BEGIN
SET NOCOUNT ON;
Declare @ SQL nvarchar (500)
Declare @ num int
If (@ mode=0)
The begin
- lookup table to obtain maximum serial number specified date statement, when the date mode to (date) (month) (year)
Set @ SQL='select isnull (Max (convert (int, the substring (' + @ listna +' len (' + @ listna + ') - '+ @ nolen + + 1,' + @ nolen +))), 0) + 1 from '+ @ tablena
+ 'where the substring (' + @ listna +' len (' + @ listna + ') - 7 - '+ @ nolen + 8)=replace (convert (varchar (8), "' + @ coltext + '", 112), "-")'
End
Else if (@ mode=1)
The begin
- lookup table to obtain maximum serial number specified date statement, when the date mode for years
Set @ SQL='select isnull (Max (convert (int, the substring (' + @ listna +' len (' + @ listna + ') - '+ @ nolen + + 1,' + @ nolen +))), 0) + 1 from '+ @ tablena
+ 'where the substring (' + @ listna +' len (' + @ listna + ') - 5 - '+ @ nolen + 6)=replace (cconvert (varchar (100),' + @ coltext +, 112), "-") '
End
The else
The begin
- lookup table to obtain maximum serial number specified date statement, when the date mode for years
Set @ SQL='select isnull (Max (convert (int, the substring (' + @ listna +' len (' + @ listna + ') - '+ @ nolen + + 1,' + @ nolen +))), 0) + 1 from '+ @ tablena
+ 'where the substring (' + @ listna +' len (' + @ listna + ') - 3 - '+ @ nolen + 4)=replace (cconvert (varchar (100),' + @ coltext +, 112), '-') '
End
- create a temporary table, to obtain maximum serial number
The create table # temp (Total int);
Insert into # temp EXEC (@ SQL);
The set @ num=(Select Total FROM # temp)
If (@ mode=0)
The begin
- joining together serial number, when the date mode to (date) (month) (year)
The set @ runningnum=@ prefix + '-' + CONVERT (varchar (100), @ coltext, 12) + '-' + replicate (' 0 ', @ nolen - len (@ num)) + the CONVERT (varchar (20), @ num)
End
Else if (@ mode=1)
The begin
- joining together serial number, when date mode for years
The set @ runningnum=@ prefix + '-' + CONVERT (varchar (4), @ coltext, 12) + '-' + replicate (' 0 ', @ nolen - len (@ num)) + the CONVERT (varchar (20), @ num)
End
The else
The begin
- joining together serial number, when the date mode for years
The set @ runningnum=@ prefix + '-' + CONVERT (varchar (2), @ coltext, 12) + '-' + replicate (' 0 ', @ nolen - len (@ num)) + the CONVERT (varchar (20), @ num)
End
Select @ runningnum
END
