Use EMSXDB
Go
begin tran
--自定义编号
declare @MyCode int 
set @MyCode  = 11
declare CardCursor Cursor for select Code,ShopCode From CRM_Card where Sn = ''
Open CardCursor --开启游标
declare @Code nvarchar(100),@ShopCode bigint
set @ShopCode =0
Fetch NEXT from CardCursor into @Code,@ShopCode
while @@FETCH_Status = 0
begin
    --临时编号
    declare @TempCode nvarchar(100)
    --生成临时编号,规则门店编号加三个0
    set @TempCode = @TempCode +convert(nvarchar(100),@ShopCode) +'000'
    --生产编号，临时编号转int加自定义编号
    set @TempCode = convert(nvarchar(100),convert(int,@TempCode)+@MyCode)
    
    --检查是否已经存在此SN
    declare @Flag bit
    
    if (select count(1) From CRM_Card where SN = @TempCode) > 0
        set @Flag = 1 
    else
        set @Flag = 0
    --循环检查
    While @flag = 1
    begin
        --生产新编号
        Set @TempCode = Convert(nvarchar(100),Convert(int,@TempCode)+1)
        --自定义编号+1
        set @MyCode = @MyCode+1
        
        if (select Count(1) From CRM_Card Where SN = @TempCode) >0
            Set @Flag = 1
        else
            Set @Flag = 0
    end
    
    --变更编号
    Update CRM_Card set SN = @TempCode where Code = @Code and ShopCode = @ShopCode
    --产生同步指令
    declare @sql nvarchar(max)
    set @sql = 'Update CRM_Card set SN = '''+@TempCode+''' where Code = '''+@Code + ''' And ShopCode = '''+@ShopCode+''''
    EXECUTE [EMSXDB].[dbo].[SYS_SyncPushToShop_InsertAsSQL] 0,@sql,0,30
    
    --打印数据
    print 'Code:'+@Code + ',新SN:'+@TempCode
    Fetch NEXT From CardCursor Into @Code,@ShopCode
End

Close CardCursor
Deallocate CardCursor
if @@error = 0
    commit tran
else
    rollback tran