用于编码/批号/单据编号自动生成的存储过程

先说下原理：

根据前缀从表中取出符合该前缀的最大单号，或者最大批号/编码

然后在尾数上+1

单号规则为：【前缀】+【日期格式】+【流水】

日期可以没有，即：【前缀】+【流水】

 

-- =============================================

-- Author: 陈恩点

-- Create date: 2020-04-18

-- Description: 自动根据前缀生成新的编码

-- =============================================

ALTER PROCEDURE [dbo].[MyRapid_AutoBatchNo]

@BatchNo NVARCHAR(100) OUTPUT,

@TableName NVARCHAR(100),

@ColumnName NVARCHAR(100),

@Heard NVARCHAR(50) = 'SN',

@Format NVARCHAR(50) = 'yyyyMMdd',

@Length INT = 4

AS

BEGIN

        DECLARE @Body NVARCHAR(100)

        DECLARE @SqlScript NVARCHAR(MAX)

DECLARE @LastBatchNo NVARCHAR(100)

        SET @Body = @Heard --+ CONVERT(NVARCHAR(50) ,GETDATE() ,112) --FORMAT(GETDATE(),@Format)

IF ISNULL(@Format ,'') != ''

BEGIN

SET @Body = @Heard + FORMAT(GETDATE(),@Format) --CONVERT(NVARCHAR(50) ,GETDATE() ,112) 

END

----取出最大的符合条件的单号

--SELECT @LastBatchNo = ISNULL(MAX(LEFT(PurchaseOrder_Name,16)),'PORD202008120000')

----取符合条件的前面几位，字符串比对是从前向后依次比对

--FROM PSI_PurchaseOrder

--WHERE PurchaseOrder_Name LIKE 'PORD20200812____%'

----最后的%允许在合法批号后面添加后缀

--AND PATINDEX('%[^0-9]%' ,SUBSTRING(PurchaseOrder_Name,13,4)) = 0

----保证流水号是数字可以递增

SET @SqlScript = 'SELECT @LastBatchNo = ISNULL(MAX(LEFT(' + @ColumnName + ','+ CONVERT(NVARCHAR(50) ,LEN(@Body) + @Length) +')),'''+@Body + REPLICATE('0' ,@Length) +''')

        FROM '+ @TableName +'

WHERE ' + @ColumnName + ' LIKE ''' + @Body + REPLICATE('_' ,@Length) + '%''                  

AND PATINDEX(''%[^0-9]%'' ,SUBSTRING('+ @ColumnName +',' + CONVERT(NVARCHAR(50),LEN(@Body) + 1) + ','+ CONVERT(NVARCHAR(50),@Length)+')) = 0' --这条件为了保证流水号是数字

        PRINT @SqlScript

        EXEC SP_EXECUTESQL @SqlScript ,N'@LastBatchNo NVARCHAR(100) OUT', @LastBatchNo OUT

DECLARE @BatchNo_Seed INT

DECLARE @BatchNo_Tail NVARCHAR(100)

SET @BatchNo_Tail = SUBSTRING(@LastBatchNo, LEN(@Body) + 1 ,@Length)

SET @BatchNo_Seed =CONVERT(INT,@BatchNo_Tail)+1

SET @BatchNo = @Body + REPLICATE ('0' ,@Length - LEN(@BatchNo_Seed) ) + CONVERT(NVARCHAR(100),@BatchNo_Seed)

END

GO