CREATE TABLE SystemSerialNo

(

    SerialNoId INT PRIMARY KEY IDENTITY,

    TableName VARCHAR(60),

    Prefix VARCHAR(20),

    LatestDate VARCHAR(8),

    MaxSerialNo INT ,

    DataVersion timestamp

)

-- Author:    <CallmeYhz,,Name>

-- Create date: <Create Date,,>

-- Description:  <Description,,>

-- [sp_GetSerialNumber] 'Test','CC',4,0

-- @ShowDate 0：编码模式 1：单号模式

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

ALTER PROCEDURE [dbo].[sp_GetSerialNumber]  @tableName varchar(100),@Prefix VARCHAR(20),@PadLeft INT,@ShowDate BIT

AS

BEGIN

 SET NOCOUNT ON;

 BEGIN TRY

   BEGIN TRAN

     DECLARE @MaxValue INT,@dataVersion timestamp,@defaultDateTime VARCHAR(8)

     IF NOT EXISTS(SELECT * FROM SystemSerialNo  WHERE tableName=@tableName AND Prefix=@Prefix)

       BEGIN

         INSERT INTO SystemSerialNo(TableName,Prefix,LatestDate,MaxSerialNo)

           VALUES (@tableName,@Prefix,CONVERT(VARCHAR(20),GETDATE(),112),1)

       END

     ELSE

       BEGIN

         SELECT @dataVersion=DataVersion,@defaultDateTime=LatestDate FROM SystemSerialNo WHERE tableName=@tableName AND Prefix=@Prefix

         --如果不显示日期，则为编码模式，最大编码加一，不受日期限制

         IF @ShowDate=0

            BEGIN

                UPDATE SystemSerialNo WITH(ROWLOCK) SET MaxSerialNo=MaxSerialNo+1

                WHERE tableName=@tableName  AND Prefix=@Prefix AND DataVersion=@dataVersion

            END

        ELSE

            BEGIN

             --如果在同一天，最大编码加一，否则更新日期并重置最大编码

             IF @defaultDateTime=CONVERT(VARCHAR(12),GETDATE(),112)

                BEGIN

                    UPDATE SystemSerialNo WITH(ROWLOCK) SET MaxSerialNo=MaxSerialNo+1

                    WHERE tableName=@tableName  AND Prefix=@Prefix AND DataVersion=@dataVersion

                END

            ELSE

                BEGIN

                    UPDATE SystemSerialNo WITH(ROWLOCK) SET LatestDate=CONVERT(VARCHAR(12),GETDATE(),112),MaxSerialNo=1

                    WHERE tableName=@tableName  AND Prefix=@Prefix AND DataVersion=@dataVersion

                END

            END

       END

     SELECT Prefix+(CASE @ShowDate WHEN 1 THEN RIGHT(LatestDate,6) ELSE '' END)+(RIGHT(replicate('0',@PadLeft)+CAST(MaxSerialNo AS VARCHAR(10)),@PadLeft))

         FROM SystemSerialNo WITH(XLOCK,PAGLOCK) WHERE tableName=@tableName  AND Prefix=@Prefix

    COMMIT TRAN

 END TRY

 BEGIN CATCH

   ROLLBACK TRAN

 END CATCH

END