实例为根据商品分类更新商品代码。
DECLARE @sortcode varchar(20), @i int, @templetesortcode varchar(20) ,@goodscode varchar(20),@flowno char(3)
select @templetesortcode = '', @i = 0,@flowno = ''
DECLARE c_base_goods CURSOR FOR SELECT goodscode,rtrim(goodssortcode) as goodssortcode FROM bak_goods where len(goodssortcode)=3 ORDER BY goodssortcode,found_date
OPEN c_base_goods
FETCH next FROM c_base_goods
INTO @goodscode, @sortcode
WHILE @@fetch_status = 0
BEGIN
IF @sortcode = @templetesortcode
begin
SELECT @i = @i+1
SELECT @flowno = convert(char(3), @i)
if @i < 10
begin
SELECT @flowno = '00'+@flowno
end
else if @i >= 10 and @i < 100
begin
SELECT @flowno = '0'+@flowno
end
else if @i >= 100
begin
SELECT @flowno = @flowno
end
UPDATE bak_goods SET goodscode = @sortcode + @flowno where goodscode = @goodscode
end
ELSE
begin
SET @templetesortcode = @sortcode
UPDATE bak_goods SET goodscode = @sortcode + '001' where goodscode = @goodscode
SET @i = 1
end
FETCH next FROM c_base_goods INTO @goodscode, @sortcode
END
CLOSE c_base_goods
DEALLOCATE c_base_goods