存储过程分页的好的例子

| 主 题： | |

有没有好的存储过程分页的好的例子！  

---|---|---
作 者： | | weihua99 (我爱DotNet)
信 誉 值： | | 80
所属论坛： | | .NET技术 ASP.NET
问题点数： | | 50
回复次数： | | 6
发表时间： | | 2004-10-12 11:01:45
| |
| |

要保证代码能够运行畅通，我看了论坛里面很多，代码都执行出错。
谢谢！

陕北吴旗娃的分页控件：

http://www.webdiyer.com

** Top **

http://207.46.156.252/china/community/Column/49.mspx

** Top **

CREATE PROCEDURE GetRecordFromPage
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsCount      bit = 0,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL   varchar(1000)     -- 主语句
declare @strTmp   varchar(300)      -- 临时变量
declare @strOrder varchar(400)      -- 排序类型

if @OrderType != 0
begin
    set @strTmp = "<(select min"
    set @strOrder = " order by [" + @fldName +"] desc"
end
else
begin
    set @strTmp = ">(select max"
    set @strOrder = " order by [" + @fldName +"] asc"
end

set @strSQL = "select top " + str(@PageSize) + " * from ["
    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
    + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
    + @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
    + @strOrder

if @strWhere != ''
    set @strSQL = "select top " + str(@PageSize) + " * from ["
        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
        + @fldName + "] from [" + @tblName + "] where (" + @strWhere + ") "
        + @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder

if @PageIndex = 1
begin
    set @strTmp = ""
    if @strWhere != ''
        set @strTmp = " where (" + @strWhere + ")"

    set @strSQL = "select top " + str(@PageSize) + " * from ["
        + @tblName + "]" + @strTmp + " " + @strOrder
end

if @IsCount != 0
    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

exec (@strSQL)
GO

** Top **

/*分页存储过程
Descript:分页存储过程
Author:Blue.Dream
Date:2004-8-18 21:01
*/
Create  PROCEDURE ListPage(
@tblName 	nvarchar(200),		         ----要显示的表或多个表的连接
@fldName 	nvarchar(200) = '*',	         ----要显示的字段列表
@pageSize	int = 10,		                  ----每页显示的记录个数
@page		int = 1,		         ----要显示那一页的记录
@pageCount	int = 1 output,	         ----查询结果分页后的总页数
@Counts	int = 1 output,		         ----查询到的记录数
@fldSort	nvarchar(100) = null,	         ----排序字段列表或条件
@Sort		bit = 0,		         ----排序方法，0为升序，1为降序
@strCondition	nvarchar(200) = null,	----查询条件,不需where
@ID		nvarchar(50)		----主表的主键
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)		----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)		----存放取得查询结果总数的查询语句
Declare @strID	 nvarchar(1000)		----存放取得查询开头或结尾ID的查询语句
Declare @sqlSort nvarchar(200)		----存放临时生成的排序条件
Declare @intCounts int			----要移动的记录数
Declare @BeginID int			----开始的ID
Declare @EndID   int			----结束的ID



--------首先生成排序方法---------
if @Sort=0		--升序
begin
	  if not(@fldSort is null)
		set @sqlSort = ' Order by ' + @fldSort	
	  else
		set @sqlSort = ' Order by ' + @ID  
end
else			--降序
begin
	if not(@fldSort is null)
		set @sqlSort = ' Order by ' + @fldSort + ' DESC'		
	else
		set @sqlSort = ' Order by ' + @ID + ' DESC '
end


--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null	--没有设置显示条件
	begin
	set @sqlTmp =  @fldName + ' From ' + @tblName
	set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName
	set @strID = ' From ' + @tblName
	end
else
	begin
	set @sqlTmp = + @fldName + 'From ' + @tblName + ' where ' + @strCondition
	set @strTmp = 'select @Counts=Count(' + @ID + ') FROM '+@tblName + ' where ' + @strCondition
	set @strID = ' From ' + @tblName + ' where ' + @strCondition
	end
	


----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out

--取得分页总数
if @Counts <= @pageSize
	set @pageCount = 1
else
	set @pageCount = (@Counts / @pageSize) + 1


--计算要移动的记录数
if @page = 1 
	set @intCounts = @pageSize
else
begin
	set @intCounts = (@page-1) * @pageSize + 1
end

-----取得分页后此页的第一条记录的ID
set @strID = 'select @BeginID=' + @ID + ' ' + @strID



set @intCounts = @intCounts - @pageSize + 1
set rowcount  @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@BeginID out

-----取得分页后此页的最后一条记录的ID
set @intCounts = @intCounts + @pageSize - 1
print @intCounts
set rowcount  @intCounts
exec sp_executesql @strID,N'@BeginID int out ',@EndID out


------恢复系统设置-----
set rowcount  0
SET NOCOUNT OFF

------返回查询结果-----
if @strCondition is null
	set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID + ' between ' + str(@BeginID) + ' and ' + str(@EndID)
else
	set @strTmp = 'select ' + @sqlTmp + ' where ' + @ID +' (between ' + str(@BeginID) + ' and ' + str(@EndID) + ')  and ' + @strCondition

if not(@sqlSort is null)
set @strTmp = @strTmp + @sqlSort
exec sp_executesql @strTmp

GO

** Top **

最好带有调用存储过程的例子。

** Top **

这是我用ASP写的你改成.NET的就好了
-------------------------------------分页的存储过程------------------------
-- 利用SQL未公开的存储过程实现分页
CREATE procedure p_splitpage   
@sql nvarchar(4000),	--要执行的sql语句
@page int=1, 			--要显示的页码
@pageSize int,		--每页的大小
@pageCount int=0 out,	--总页数
@recordCount int=0 out	--总记录数
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
	,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize 
exec sp_cursorclose @p1
GO
---------------------------得到执行的查询语句的存储过程-----------------
CREATE PROCEDURE dtalyjd_search (@search_name varchar(50)) AS
DECLARE @str varchar(4000)
	if @search_name=''
		begin
			set @str='select * from 表名'
			select @str sql
			return
		end
	else
		begin
			set @str='select * from 表名 where 
search_name like ''%'+@search_name+'%'''
			select @str sql
			return
		end
GO
--------------------------ASP页面---------------------
```

	dim search_name
	search_name=trim(request("search_name"))
	set rs1=conn.execute("exec dtalyjd_search '"&search_name&"'")
	sql=rs1("sql")
	rs1.close
	if request("page")="" or request("page")=0 or IsNumeric(request("page"))=false then
		m_page=1		
	else
		m_page=cint(request("page"))
	end if
	m_pagesize=14'每页的条数
	set cmd = server.CreateObject("adodb.command")
    cmd.ActiveConnection = conn
    cmd.CommandType = 4
    cmd.CommandText = "p_SplitPage"
    cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql)
    cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, m_page)
    cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, m_pageSize)
    cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, m_pageCount)
    cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, m_recordCount)
    set rs = cmd.Execute
	set rs = rs.NextRecordSet
	m_pageCount = cmd.Parameters("@pageCount").value
    m_recordCount = cmd.Parameters("@recordCount").value
	if m_pageCount = 0 then m_pageCount = 1
	if m_page>m_pageCount then 
		response.Redirect("bureauser_result.asp?page="&m_pageCount&"&lxsuser_name="&lxsuser_name) 
	end if
	set rs = cmd.Execute

查询结果共 [8] 页 这是第 [5] 页

``` if m_page&amp;lt;&amp;gt;1 then 1 <td width="23%"><img height="8" src="images/first.gif" width="8"/> <a =myself="" =search_name="" ```="" ```?page="1&amp;search_name=```" href="```">首页</a></td> 2<td width="31%"><img height="8" src="images/pre.gif" width="8"/> <a =(page-1)="" =myself="" =search_name="" ```="" ```&search_name="```" ```?page="```" href="```">上一页</a></td> 3 ``` 4 5 end if 6 If m_page &amp;lt;&amp;gt; m_pageCount Then 7 <td width="24%"><img height="8" src="images/lat.gif" width="8"/><a =(m_page+1)="" =myself="" =search_name="" ```="" ```&search_name="```" ```?page="```" href="```">下一页</a></td> ``` end if `````` end if ``` 尾页