DECLARE @num int
DECLARE @Count int
DECLARE @Total int
DECLARE @dat varchar(12)
set @num=1
set @Count=0
set @Total=(select count(*) from OA07_Book where type='SW')
while (@num <= @Total)
begin
if (object_id('tempdb..##m') is not null)
drop table ##m
if (object_id('tempdb..##n') is not null)
drop table ##n
exec('select top '+@num+' oldcode into ##m from OA07_Book where type=''SW'' order by oldcode')
select top 1 oldcode into ##n from ##m order by oldcode desc
set @Count=(select count(code) from OA07_Book where type='SW' and code like 'SW-%') + 1
if @Count<10
set @dat='SW-00'+cast(@Count as varchar)
else if @Count<100
set @dat='SW-0'+cast(@Count as varchar)
else
set @dat='SW-'+cast(@Count as varchar)
update oa07_book set code=@dat where oldcode=(select top 1 oldcode from ##n)
set @num=@num+1
end
drop table ##m
drop table ##n