我怎么样才能一次从数据库里提取多行数据FETCH每次好像只能返回一行信息
我用了一个while循环,但怎样在客户端得到每次fetch的数据呢?
DECLARE @name char(50),
@id int,@pass char,@i int,@kz int,@bz int,
@count int,@j int,@page int,@pagecount int,@zfc char(100)
set @count=cast((select count(id) as a from test) as int)
set @page=@count/5 --一页分5条
set @j=@count%5
select @j,@count
if(@j>0)
begin
set @page=@page+1
end
set @kz=2 --这里控制页数set @pagecount=@count-(5*@kz)+1
if(@pagecount<=0)
begin
set @pagecount=1
end
DECLARE Employee_Cursor SCROLL CURSOR FOR SELECT * FROM test
OPEN Employee_Cursor
select @@CURSOR_ROWS
FETCH RELATIVE @pagecount FROM Employee_Cursor --从最后行开始
--INTO @id, @name,@pass
set @i=1
while @@FETCH_STATUS=0
begin
FETCH next FROM Employee_Cursor
set @i=@i+1
if (@i=5)
begin
break
end
end
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
我用了一个while循环,但怎样在客户端得到每次fetch的数据呢?
DECLARE @name char(50),
@id int,@pass char,@i int,@kz int,@bz int,
@count int,@j int,@page int,@pagecount int,@zfc char(100)
set @count=cast((select count(id) as a from test) as int)
set @page=@count/5 --一页分5条
set @j=@count%5
select @j,@count
if(@j>0)
begin
set @page=@page+1
end
set @kz=2 --这里控制页数set @pagecount=@count-(5*@kz)+1
if(@pagecount<=0)
begin
set @pagecount=1
end
DECLARE Employee_Cursor SCROLL CURSOR FOR SELECT * FROM test
OPEN Employee_Cursor
select @@CURSOR_ROWS
FETCH RELATIVE @pagecount FROM Employee_Cursor --从最后行开始
--INTO @id, @name,@pass
set @i=1
while @@FETCH_STATUS=0
begin
FETCH next FROM Employee_Cursor
set @i=@i+1
if (@i=5)
begin
break
end
end
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[password] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
declare @FNam varchar(40)
fetch next from authors into @LNam,@FNam通过@LNam,@FNam可以取数,也可以通过output传出去