SQLServer流水号自动生成

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

最近给客户做生成条码的功能时，碰到个问题，需要根据数量自动生成流水号，然后加上客户指定的前缀，组合成条码。

折腾了一会，最后通过个存储过程实现。

--@Prefix 指定前缀，@InitialVal 流水号起始值，@IncrementVal 流水号递增值，@TotalNum 流水号总数，@BitNum 流水号位数

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id=object_id(N'[dbo].[SP_GenerateSerialNo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].SP_GenerateSerialNo
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE SP_GenerateSerialNo @Prefix nvarchar(50), @InitialVal int, @IncrementVal int, @TotalNum int, @BitNum int
AS
DECLARE @SQL nvarchar(1000)
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id=object_id('tempdb..##t'))
DROP TABLE ##t
SET @SQL=N'SELECT TOP '+CONVERT(nvarchar(10),@TotalNum)+' id=IDENTITY(INT,'+CONVERT(nvarchar(4),@InitialVal)+','+CONVERT(nvarchar(4),@IncrementVal)+') INTO ##t FROM sysobjects,syscolumns'
--print @SQL
EXEC sp_executesql @SQL
SELECT LTRIM(RTRIM(@Prefix))+SUBSTRING(ret, LEN(ret)-@BitNum+1, @BitNum) as SerialNo FROM (
SELECT '00000000000000000000'+CONVERT(nvarchar(10), id) as ret FROM ##t) a
--SELECT * FROM ##t
DROP TABLE ##t
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO