I haven't been here for a long time. Share a stored procedure that automatically generates numbers

Set quoted_identifier on
Go
Set ansi_nulls on
Go
Alter procedure pgetnewkey
@ Ctablename varchar (20) = 'torderform ',
@ Ckeyfield varchar (15) = 'ccode ',
@ Ikeylength integer = 12,
@ Cresult varchar (15) Output
As
Declare @ cCode varchar (20), @ Cmax varchar (20), @ cserial varchar (4), @ csql varchar (150), @ czero varchar (8)

Create Table # A (cmaxcode varchar (10) null)

If @ ikeylength> 10
Begin
Set @ czero = substring ('20140901', 1, @ iKeyLength-8)
Set @ csql =
'Insert into # A select max (substring ('+ @ ckeyfield +', 9, 4) cmaxcode from '+ @ ctablename +
'Where substring ('+ @ ckeyfield +', 112) = convert (char (8), getdate )'
Exec (@ csql)

Select @ Cmax = cmaxcode from #

If @ Cmax is null
Set @ Cmax = '0'

Set @ cserial = convert (varchar (4), convert (INT, @ Cmax) + 1)

Set @ cCode = convert (char (8), getdate (), 112) + stuff (@ czero, @ iKeyLength-8 + 1-len (@ cserial), Len (@ cserial ), @ cserial)
End
Else
Begin
Set @ czero = substring ('20140901', 1, @ ikeylength)
Set @ csql =
'Insert into # A select max ('+ @ ckeyfield +') cmaxcode from '+ @ ctablename

Exec (@ csql)

Select @ Cmax = cmaxcode from #

If @ Cmax is null
Set @ Cmax = '0'

Set @ cserial = convert (varchar (4), convert (INT, @ Cmax) + 1)

Set @ cCode = stuff (@ czero, @ ikeylength + 1-len (@ cserial), Len (@ cserial), @ cserial)
End

Set @ cresult = @ cCode

 

 

 

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go