Sql產生不重複的數字

標籤：

-- =============================================-- Author:TUZI-- Create date: 2016.4.18-- Description:組建活動邀約碼(活動編碼+Code是唯一)/* exec sp_generateInvitecode   @CampaignCode=‘test‘ ,@Length=10,@Quantity=100000 */-- =============================================ALTER PROCEDURE sp_generateInvitecode(@CampaignCode nvarchar(50)                                      ,--所屬活動編碼                                       @Length int                                      ,--Code的長度                                       @Quantity int--Code的數量)ASBEGIN    CREATE TABLE #t(Code nvarchar(50));    CREATE UNIQUE INDEX idx_union_code ON #t(Code)    DECLARE @batchNum nvarchar(50);    SET @batchNum = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(19),GETDATE(),121),‘ ‘,‘‘),‘-‘,‘‘),‘:‘,‘‘),‘.‘,‘‘)    --如果活動進行中,需要繼續產生邀請碼，就得排除已經存在於表中的碼    SELECT Code INTO #existsCode      FROM  XXX      WHERE CampaignCode = @CampaignCode    DECLARE @runQ int = 0;    WHILE @runQ < @Quantity        BEGIN            INSERT INTO #t            SELECT TOP 10000 LEFT(CAST(LEFT(‘10000000000‘,@Length)AS bigint) + ABS(CHECKSUM(NEWID())),@Length)              FROM syscolumns c1,syscolumns c2;            DELETE #t            WHERE Code IN(SELECT Code                            FROM #existsCode);            SELECT @runQ = COUNT(DISTINCT Code)              FROM #t;        END    INSERT INTO  XXX(CampaignCode                            ,Code                            ,BatchNum                            ,createTime)    SELECT TOP (@Quantity)@CampaignCode                         ,Code                         ,@batchNum                         ,GETDATE()      FROM #t    DROP TABLE #t;ENDGO

　　

Sql產生不重複的數字