求一条好用的分页mssql存储过程 谢谢！！
求一条好用的性能好的分页mssql存储过程，最好能支持表连接 非常感谢大侠们帮助！！急~~~~ --------------------编程问答-------------------- 结贴率。。不能太楼主 --------------------编程问答-------------------- 顶楼主的结贴率：（转帖）
--------------------编程问答-------------------- 楼上的大哥 您那个不能执行啊 建成功了 但是执行失败了 --------------------编程问答--------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--阿会楠sosuo8.com根据网络上的代码进行了修改，版权归原作者所有2009-4-5
--修改输出总记录数
CREATE PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@OrderType bit, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500), -- 查询条件 (注意: 不要加 where)
@rowCount int output --查询到的记录数
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @strCountTmp nvarchar(100) --记录数
declare @timediff datetime --耗时
begin
select @timediff = Getdate()
if @strWhere !=''
set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + '] where '+@strWhere
else
set @strCountTmp = 'select @rowCount = rowCount_tmp from tmp where (table_tmp = ''' + @tblName +''')'
exec sp_executesql @strCountTmp,N'@rowCount int out',@rowCount out --输出总记录数
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' 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) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
select datediff(ms,@timediff,GetDate()) as runtime
--------------------编程问答--------------------
CREATE procedure [dbo].[PageInation]
@tblName varchar(225),--表名
@strGetFields varchar(1000)='*',--需要返回的列
@fldName varchar(50),--排序的字段名
@pageSize int=3,--页尺寸
@pageIndex int=1,--页码
@totalCount int=0 output,--表的总记录行
@orderType bit=0,--设置排序类型，非值为降序
@strWhere varchar(1500)=''--查询条件（注意：不要加where）
as
declare @strSql varchar(5000)--主语句
declare @strTmp varchar(110)--临时变量
declare @strOrder varchar(400)--排序类型
declare @sql nvarchar(500)
declare @param nvarchar(100)
if @strWhere=''
begin
set @sql=N'select @totalCount=count(*) from '+@tblName--注意：from后面必须有空格
set @param=N'@totalCount int output'
execute sp_executesql @sql,@param,@totalCount=@totalCount output
end
else
begin
set @sql=N'select @totalCount=count(*) from '+@tblName+'where'+@strWhere --注意：from 后面必须有空格
set @param=N'@totalCount int output'
execute sp_executesql @sql,@param,@totalCount=@totalCount output
end
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
if @pageIndex=1
begin
if @strWhere!=''
set @strSql='select top '+STR(@pageSize)+''+@strGetFields+' from ['+@tblName+'] where '+@strWhere+ ' ' +@strOrder
else
set @strSql='select top '+STR(@pageSize)+''+@strGetFields+' from ['+@tblName+']'+@strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSql以真正执行的SQL代码
if @strWhere = ''
set @strSql = 'select top '+STR(@pageSize)+' '+@strGetFields+'
from '+@tblName+' where '+@fldName+' '+@strTmp+' ('+@fldName+')
from (select top '+STR((@pageIndex - 1)*@pageSize)+' '+@fldName+'
from '+@tblName+' '+@strOrder+') as tblTmp)' + @strOrder
else
set @strSql = 'select top '+STR(@pageSize)+' '+@strGetFields+'
from ['+@tblName+'] where ['+@fldName+']'+@strTmp+' (['+@fldName+'])
from (select top '+STR((@pageIndex - 1)*@pageSize)+' ['+@fldName+']
from ['+@tblName+'] where ' +@strWhere+' '+@strOrder+') as tblTmp) and '+@strWhere+' '+@strOrder
end
exec (@strSql)
--------------------编程问答--------------------
/*===============================================
declare @PageCount int,@TotalRecord int
exec SYS_PageData 'SELECT * FROM tb' , 1 ,10,@PageCount out,@TotalRecord out
select @PageCount,@TotalRecord
===============================================*/
CREATE PROCEDURE SYS_PageData
(
@SqlCommandText nvarchar(4000),
@CurrentPage int, -- 頁次
@PageSize int, -- 行數
@PageCount int out ,
@TotalRecord int out
)
AS
set nocount on
DECLARE
@P1 int, --P1是游標的id
@rowcount int
SET @SqlCommandText=replace(replace(replace(replace(replace(replace(replace(replace(@SqlCommandText,'exec ',''),'insert ',''),'delete ',''),'update ',''),'drop ',''),'alter ',''),'chr(',''),'mid(','')
exec sp_cursoropen @P1 output ,@SqlCommandText,@scrollopt=1,@ccopt=1,@rowcount=@TotalRecord output
-- 頁數 行數
set @CurrentPage=(@CurrentPage-1)*@PageSize+1
set @PageCount = ceiling(1.0*@TotalRecord/@PageSize)
exec sp_cursorfetch @P1,16,@CurrentPage,@PageSize
exec sp_cursorclose @P1
set nocount off
GO
set @strCountTmp = 'select @rowCount = rowCount_tmp from tmp where (table_tmp = ''' + @tblName +''')'你的困惑在这里
tmp表用来存放无搜索条件时的总记录数。这里我要说下，为什么需要一个表用来专门存放总记录数，总记录数你可以在后台每隔一段时间就去更新一次，把最新的总记录数写进去。否则的话，520W的记录你每次都要用count(id)那么耗费的时间也不少。
CREATE TABLE [dbo].[tmp](
[id] [int] IDENTITY(1,1) NOT NULL,
[rowCount_tmp] [int] NULL,
[table_tmp] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_tmp] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
采用aspnetpager这个控件，这个控件我们只需要传入3个值
http://sosuo8.com/article/show.asp?id=2669
--------------------编程问答--------------------
--------------------编程问答-------------------- 这结贴率也太高了。 --------------------编程问答-------------------- 你们写的存储过程 我试了 如果我要按 price这列排序 单这列价格肯定会有重复的 这样分页出来的会有很多数据没有 不准 --------------------编程问答-------------------- 简单的说 就是如果排序的列 数据存在重复的 那分完也后有可能少数据
CREATE PROCEDURE [sp_Paging]
(
@SQL nvarchar(1024) = '', --查询语句
@PageSize int = 20, --分页大小
@PageIndex int = 0, --分页索引
@Sort nvarchar(100) = '', --排序字段
@TotalCount int = 0 output --总数
)
AS
set nocount on
/*声明查询字符串*/
declare @strSQL nvarchar(4000)
set @strSQL = ' select @TotalCount=count(*) from ('+@SQL+') as t '
/*取得查询结果总数*/
exec sp_executesql
@strSQL,
N'@TotalCount int=0 OUTPUT',
@TotalCount=@TotalCount OUTPUT
declare @ItemCount int
declare @_PageIndex int
set @_PageIndex = @PageIndex + 1;
/*确定搜索边界*/
set @ItemCount = @TotalCount - @PageSize * @_PageIndex
if(@ItemCount < 0)
set @ItemCount = @ItemCount + @PageSize
else
set @ItemCount = @PageSize
if(@ItemCount < 0) return 1
if(@Sort != '')
begin
/*声明排序变量*/
declare @IndexSort1 nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2 nvarchar(50)
SET @Sort1 = @Sort
SET @Sort2 = Replace(Replace(Replace(@Sort, 'DESC', '@SORT'), 'ASC', 'DESC'), '@SORT', 'ASC')
set @strSQL = 'SELECT * FROM
(SELECT TOP ' + STR(@ItemCount) + ' * FROM
(SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
('+@SQL+') AS t0
ORDER BY '+@Sort1 +') AS t1
ORDER BY '+@Sort2 +') AS t2
ORDER BY ' +@Sort
end
else
begin
set @strSQL = 'SELECT * FROM
(SELECT TOP ' + STR(@ItemCount) + ' * FROM
(SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
('+@SQL+') As t0)
aS t1)
AS t2'
end
exec sp_executesql
@strSQL
GO
--------------------编程问答--------------------
大哥，你也看看存储过程，那个排序列是数据表中可以做主键的列，这样才能取出一定位置一定数量的数据行。 --------------------编程问答-------------------- http://www.webdiyer.com
补充：.NET技术 , ASP.NET
