使用游标的释放问题

[morningnet]

declare @xh nvarchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT xh from xuesheng where bh='20042班'
OPEN authors_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM authors_cursor INTO @xh
insert into table1(xh,kc) values(@xh,'数学')
end

CLOSE authors_cursor
DEALLOCATE authors_cursor

我在查询分析器中使用,第一次执行,成功插入本班所有人的数学科目记录,换一个班别如'20043班',该语句就不发挥作用了,什么都不执行,关闭查询分析器重新打开一个,粘铁进上面的代码并改变班别,又能够执行,反正就是打开一次查询分析器就能执行一次改游标,不知道为什么??难道DEALLOCATE authors_cursor不能释放游标?

[xeqtr1982]

declare @xh nvarchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT xh from xuesheng where bh='20042班'
OPEN authors_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
insert into table1(xh,kc) values(@xh,'数学')
FETCH NEXT FROM authors_cursor INTO @xh
end

CLOSE authors_cursor
DEALLOCATE authors_cursor

[itblog]

应该这样写吧~


declare @xh nvarchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT xh from xuesheng where bh='20042班'
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @xh
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table1(xh,kc) values(@xh,'数学')
FETCH NEXT FROM authors_cursor INTO @xh
end

CLOSE authors_cursor
DEALLOCATE authors_cursor

[xeqtr1982]

declare @xh nvarchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT xh from xuesheng where bh='20042班'
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @xh
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table1(xh,kc) values(@xh,'数学')
FETCH NEXT FROM authors_cursor INTO @xh
end

CLOSE authors_cursor
DEALLOCATE authors_cursor
--晕，上面还少个fetch

[huailairen]

declare @xh nvarchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT xh from xuesheng where bh='20042班'
试着打印输出 看第二次有没有正常执行
OPEN authors_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM authors_cursor INTO @xh
insert into table1(xh,kc) values(@xh,'数学')
print cast(@xh as int)-----------------------------------输出
end

CLOSE authors_cursor
DEALLOCATE authors_cursor

[点点星灯]

declare @xh nvarchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT xh from xuesheng where bh='20042班'
OPEN authors_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
--FETCH NEXT FROM authors_cursor INTO @xh
insert into table1(xh,kc) values(@xh,'数学')
end
fetch next from authors_cursor into @xh --楼主,这一句不对,应该是在这里吧
CLOSE authors_cursor
DEALLOCATE authors_cursor

[点点星灯]

--给个例子参考

请教一个问题:
有如下一个表:
货号, 毛需求,订单供给,仓存供给,
101 500 0 0
101 0 100 0
101 0 0 400
要得到如下表:(要用什么sql语句???)
货号, 毛需求 订单供给,仓存供给 , 累计可用量
101 500 0 0 -500
101 0 100 0 -400
101 0 0 400 0

累计可用量 = 上一个累计可用量 + 订单供给 + 仓存供给-毛需求

declare @a table(货号 int,毛需求 int,订单供给 int,仓存供给 int)
insert @a(货号,毛需求,订单供给,仓存供给)
values(101,500,0,0)
insert @a(货号,毛需求,订单供给,仓存供给)
values(101,0,100,0)
insert @a(货号,毛需求,订单供给,仓存供给)
values(101,0,0,400)
insert @a(货号,毛需求,订单供给,仓存供给)
values(101,200,0,400)
select * from @a


declare @b table(货号 int,毛需求 int,订单供给 int,仓存供给 int,累计 int)
declare @num int
set @num=0

declare @a1 int
declare @a2 int
declare @a3 int
declare Num_Cursor CURSOR FOR --游标的定义
select 毛需求,订单供给,仓存供给 from @a
open Num_Cursor --打开游标
fetch next from Num_Cursor into @a1,@a2,@a3
while @@FETCH_STATUS=0
begin
set @num=@num+@a2+@a3-@a1
insert @b(毛需求,订单供给,仓存供给,累计)
values(@a1,@a2,@a3,@num)
fetch next from Num_Cursor into @a1,@a2,@a3 --循环
end
close Num_Cursor --关闭游标
deallocate Num_Cursor --销毁游标


select * from @b