while(@@fetch_status=0)
begin
select @id=9999999*rand()
while @id not in (select cpid from date_test where left(end_time,10)=left(getdate(),10))
begin
select @id=99999999*rand()
if @id in (select cpid from date_test )
begin
update date_test set cpid=@id
where current of cur_updateid
fetch next from cur_updateid into @id
end
else
begin
continue
end
end
end
OK,我现在把我朋友帮我搞定的东西拿出来,大家一起研究,探讨
也希望大家能够继续努力!谢谢各位!下面就我是朋友做的东西:
创建表:
create table date_test
(
cpid varchar(20),
name varchar(20),
end_time datetime
)
select* from date_test
添加数据:当执行时,满足end_time为当前时,自动更新ID,并且end_time向后加一个月,即:成为了2005-2-20
insert into date_test values('232323','mei','2005-1-20')
insert into date_test values('121212','mei','2005-1-20')
insert into date_test values('2004313','nl','2005-1-20')
insert into date_test values('2004314','nl','2004-12-20')
/****************************************************/
/****************************************************/
/****************************************************/
创建存储过程,调用了游标:
CREATE procedure chagerecord
as
/*定义一个游标来存放查询到符合条件的记录*/
set nocount on
declare @upid varchar(20)
declare @tempuid varchar(20)
declare @temprnd varchar(4)
declare resultrecord cursor for
select cpid from date_test where left(end_time,12)=left(getdate(),12)
open resultrecord
fetch next from resultrecord into @upid
while @@fetch_status=0
begin
set @tempuid= ltrim(str(year(getdate())))+ltrim(str(month(getdate())))+ltrim(str(day(getdate())))
set @temprnd=round(9999*rand(),0)
set @tempuid=ltrim(str(@tempuid))+@temprnd
update date_test set cpid=@tempuid,end_time=dateadd(mm,1,getdate()) where cpid=@upid
fetch next from resultrecord into @upid
end
close resultrecord
deallocate resultrecord
GO
/********************************** 你的代码 ************************/
select @id=9999999*rand()
while @id not in (select cpid from date_test where left(end_time,10)=left(getdate(),10))
begin
select @id=99999999*rand()
if @id in (select cpid from date_test )
begin
update date_test set cpid=@id
where current of cur_updateid
fetch next from cur_updateid into @id
end
else
begin
continue
end
end
/***************************** 改成 ********************************/
while exists(select cpid from date_test where cpid=@id)
set @id=9999999*rand()
update date_test set cpid=@id
where current of cur_updateid
fetch next from cur_updateid into @id