create procedure mytest @index int, @num int as create table #(id int,[name] varchar(50),age int ,sex varchar(50)) --声明的表 declare mycursor cursor Scroll for select * from dbo.userInFo --声明的游标declare @count int--声明的计数变量 set @count=0 open mycursor--打开游标 fetch Absolute @index from mycursorwhile @count<@num begin insert # exec('fetch next from mycursor')-- into @table set @count=@count+1 endclose mycursor--关闭游标 deallocate mycursor select * from # go
/*游标分页查找数据*/ CREATE PROCEDURE [dbo].[GetRecordSet] @strSql VARCHAR(8000), --查询sql,如select * from [user] @PageIndex INT, --查询当页号 @PageSize INT--每页显示记录 AS SET NOCOUNT ON DECLARE @p1 INT DECLARE @currentPage INT SET @currentPage = 0 DECLARE @RowCount INT SET @RowCount = 0 DECLARE @PageCount INT SET @PageCount = 0 EXEC sp_cursoropen @p1 OUTPUT, @strSql, @scrollopt=1, @ccopt=1, @rowcount=@rowCount OUTPUT --得到总记录数 SELECT @PageCount = CEILING(1.0*@rowCount/@pagesize) --得到总页数 , @currentPage = (@PageIndex-1)*@PageSize+1 SELECT @RowCount, @PageCount EXEC sp_cursorfetch @p1, 16, @currentPage, @PageSize EXEC sp_cursorclose @p1 SET NOCOUNT OFF GO --调用: --EXEC [dbo].[GetRecordSet] '任何条件的SQL语句',2,10 EXEC [dbo].[GetRecordSet] 'select * from tb', 2, 2
/*分页查找数据*/ CREATE PROCEDURE [dbo].[GetRecordSet] @strSql varchar(8000),--查询sql,如select * from [user] @PageIndex int,--查询当页号 @PageSize int--每页显示记录 AS set nocount on declare @p1 int declare @currentPage int set @currentPage = 0 declare @RowCount int set @RowCount = 0 declare @PageCount int set @PageCount = 0 exec sp_cursoropen @p1 output,@strSql,@scrollopt=1,@ccopt=1,@rowcount=@rowCount output --得到总记录数 select @PageCount=ceiling(1.0*@rowCount/@pagesize) --得到总页数 ,@currentPage=(@PageIndex-1)*@PageSize+1 select @RowCount,@PageCount exec sp_cursorfetch @p1,16,@currentPage,@PageSize exec sp_cursorclose @p1 set nocount off GO本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/28/4740536.aspx
@index int,
@num int
as
create table #(id int,[name] varchar(50),age int ,sex varchar(50))
--声明的表
declare mycursor cursor Scroll
for select * from dbo.userInFo
--声明的游标declare @count int--声明的计数变量
set @count=0
open mycursor--打开游标
fetch Absolute @index from mycursorwhile @count<@num
begin
insert # exec('fetch next from mycursor')-- into @table
set @count=@count+1
endclose mycursor--关闭游标
deallocate mycursor
select * from #
go
CREATE PROCEDURE [dbo].[GetRecordSet]
@strSql VARCHAR(8000), --查询sql,如select * from [user]
@PageIndex INT, --查询当页号
@PageSize INT--每页显示记录
AS
SET NOCOUNT ON
DECLARE @p1 INT
DECLARE @currentPage INT
SET @currentPage = 0
DECLARE @RowCount INT
SET @RowCount = 0
DECLARE @PageCount INT
SET @PageCount = 0
EXEC sp_cursoropen @p1 OUTPUT, @strSql, @scrollopt=1, @ccopt=1, @rowcount=@rowCount OUTPUT --得到总记录数
SELECT @PageCount = CEILING(1.0*@rowCount/@pagesize) --得到总页数
, @currentPage = (@PageIndex-1)*@PageSize+1
SELECT @RowCount, @PageCount
EXEC sp_cursorfetch @p1, 16, @currentPage, @PageSize
EXEC sp_cursorclose @p1
SET NOCOUNT OFF
GO
--调用:
--EXEC [dbo].[GetRecordSet] '任何条件的SQL语句',2,10 EXEC [dbo].[GetRecordSet] 'select * from tb', 2, 2
CREATE PROCEDURE [dbo].[GetRecordSet]
@strSql varchar(8000),--查询sql,如select * from [user]
@PageIndex int,--查询当页号
@PageSize int--每页显示记录
AS
set nocount on
declare @p1 int
declare @currentPage int
set @currentPage = 0
declare @RowCount int
set @RowCount = 0
declare @PageCount int
set @PageCount = 0
exec sp_cursoropen @p1 output,@strSql,@scrollopt=1,@ccopt=1,@rowcount=@rowCount output --得到总记录数
select @PageCount=ceiling(1.0*@rowCount/@pagesize) --得到总页数
,@currentPage=(@PageIndex-1)*@PageSize+1
select @RowCount,@PageCount
exec sp_cursorfetch @p1,16,@currentPage,@PageSize
exec sp_cursorclose @p1
set nocount off
GO本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/10/28/4740536.aspx