ALTER PROCEDURE dbo.StoredProcedure1
AS
declare mycur1 cursor for select psequence,pstime from p_message where pstime>'2008-9-22' for update of pstime
declare @pseq nvarchar(12)
declare @num int
declare @pt smalldatetime
BEGINopen mycur1fetch next from mycur1 into @pseq,@ptwhile @@FETCH_STATUS=0
begin
set @num = floor(rand()*500)
update p_message set pstime=dateadd(n,@num,getdate()) where CURRENT OF mycur1
fetch next from mycur1 into @pseq,@pt
end delete mycur1
deallocate mycur1RETURN
END我用sql server2005的查询分析器执行,但结果是它输出n个p_message表的查询结果集,输出结果集后告诉我类似:【(162 行受影响)(返回 2623 行)】这样的信息,然后继续循环输出,最终内存都要不够用了,拜托大家多帮忙。
AS
declare mycur1 cursor for select psequence,pstime from p_message where pstime>'2008-9-22' for update of pstime
declare @pseq nvarchar(12)
declare @num int
declare @pt smalldatetime
BEGINopen mycur1fetch next from mycur1 into @pseq,@ptwhile @@FETCH_STATUS=0
begin
set @num = floor(rand()*500)
update p_message set pstime=dateadd(n,@num,getdate()) where CURRENT OF mycur1
fetch next from mycur1 into @pseq,@pt
end delete mycur1
deallocate mycur1RETURN
END我用sql server2005的查询分析器执行,但结果是它输出n个p_message表的查询结果集,输出结果集后告诉我类似:【(162 行受影响)(返回 2623 行)】这样的信息,然后继续循环输出,最终内存都要不够用了,拜托大家多帮忙。
AS
declare mycur1 cursor for select psequence,pstime from p_message where pstime>'2008-9-22' for update of pstime
declare @pseq nvarchar(12)
declare @num int
declare @pt smalldatetime
open mycur1
fetch next from mycur1 into @pseq,@pt
while @@FETCH_STATUS=0
begin
set @num = floor(rand()*500)
update p_message set pstime=dateadd(n,@num,getdate()) where CURRENT OF mycur1
fetch next from mycur1 into @pseq,@pt
end
delete mycur1
deallocate mycur1
delete mycur1 可以吗2000要用close mycur1
insert p_message select '1',getdate()
insert p_message select '1',getdate()
insert p_message select '1',getdate()
;go
create proc dbo.StoredProcedure1
as
begin
declare mycur1 cursor for
select psequence,pstime from p_message where pstime>'2008-9-21' for update of pstime
declare @pseq nvarchar(12)
declare @num int
declare @pt smalldatetime
open mycur1 fetch next from mycur1 into @pseq,@pt while @@FETCH_STATUS=0
begin
set @num = floor(rand()*500)
update p_message set pstime=dateadd(n,@num,getdate()) where CURRENT OF mycur1
fetch next from mycur1 into @pseq,@pt
end close mycur1
deallocate mycur1
end
go
;
exec dbo.StoredProcedure1 select * from p_message
drop table p_message
drop proc dbo.StoredProcedure1 /*
psequence pstime
------------ -----------------------
1 2008-09-23 16:44:20.440
1 2008-09-23 16:29:20.450
1 2008-09-23 18:18:20.450(3 行受影响)
*/