22,207
社区成员
发帖
与我相关
我的任务
分享
DECLARE @test CURSOR
FOR SELECT * FROM #testtable
OPEN @test
DECLARE @i
set @i = 1
WHILE @i > 0
BEGIN
......
FETCH NEXT FROM @test INTO ...
WHILE @@FETCH_STATUS = 0
BEGIN
......
FETCH NEXT FROM @test INTO ...
END
CLOSE @test
DEALLOCATE @test
END
直接这样不就行了吗,为啥要关了再开啊[/quote]
我是要循环这个表n次
试过不重复打开,发现没用。--------------- 创建测试的临时表 BEGIN -------------------
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SET NOCOUNT ON
--插入 10 行随机字符串到临时表
SELECT LEFT(NEWID(),5) AS n
INTO #tmp
FROM MASTER.dbo.spt_values AS sv
WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 10
--------------- 创建测试的临时表 END -------------------
--为临时表添加一个序号列,并作为主键
ALTER TABLE #tmp ADD rowNum INT IDENTITY(1,1) NOT NULL
ALTER TABLE #tmp ADD CONSTRAINT PK_tmp_xxx PRIMARY KEY (rowNum)
DECLARE @i INT,@iMax INT,@n VARCHAR(20)
SELECT @i=1,@iMax=MAX(rowNum) FROM #tmp
--以序号列来循环
WHILE @i<=@iMax
BEGIN
SELECT @n=n FROM #tmp WHERE rowNum=@i
PRINT CAST(@i AS VARCHAR(10))+' '+@n
SET @i=@i+1
END
/*
1 1EDAB
2 30421
3 3B28A
4 D3CE7
5 594EA
6 989E5
7 83E98
8 EFE96
9 57C00
10 ED03C
*/
DECLARE @test CURSOR
FOR SELECT * FROM #testtable
OPEN @test
DECLARE @i
set @i = 1
WHILE @i > 0
BEGIN
......
FETCH NEXT FROM @test INTO ...
WHILE @@FETCH_STATUS = 0
BEGIN
......
FETCH NEXT FROM @test INTO ...
END
CLOSE @test
DEALLOCATE @test
END
直接这样不就行了吗,为啥要关了再开啊