Question

sql 存储过程分页(asp.net)

希望能处理百万级数据。功能稍微强大些，使用方便。。不要从网上滥用粘贴，，能用就行。

Answer 1

------------------------------------
--用途：分页存储过程
--说明：
------------------------------------Create PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '1=1' -- 查询条件 (注意: 不要加 where)
ASdeclare @begin int ,@end int,@sql varchar(4000),@order varchar(5)set @begin=@PageSize* (@PageIndex-1) +1
set @end=@begin+@PageSize-1
set @order=''
if(@OrderType=1)
set @order=' desc'set @sql='
select * from
(
select
ROW_NUMBER() over(order by ' + @fldName + @order +' ) as od,
*
from '+@tblName +'
where ' + @strWhere +
') as tbl
where od between ' + rtrim(ltrim(str(@begin)))+' and ' + rtrim(ltrim(str(@end)))exec (@sql)
if(@IsReCount=1)
exec('select count(*) from '+ @tblName+' where ' +@strWhere) 我们老师给我们的.....

Answer 2

2000的ALTER PROCEDURE [dbo].[dl_admin_GetPaged]
( @WhereClause varchar (2000) , @OrderBy varchar (2000) , @PageIndex int , @PageSize int
)
AS

BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize -- Create a temp table to store the select results
Create Table #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[WorkNo] varchar(50) COLLATE database_default
)

-- Insert into the temp table
declare @SQL as nvarchar(4000)
SET @SQL = 'INSERT INTO #PageIndex (WorkNo)'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END
SET @SQL = @SQL + ' [WorkNo]'
SET @SQL = @SQL + ' FROM dbo.[dl_admin]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF LEN(@OrderBy) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END

-- Populate the temp table
exec sp_executesql @SQL -- Return paged results
SELECT O.[WorkNo], O.[RoleID], O.[AdminName], O.[AdminSex], O.[AdminPhone], O.[AdminMobile], O.[AdminOfficePhone], O.[AdminRemark], O.[DelFlag]
FROM
dbo.[dl_admin] O,
#PageIndex PageIndex
WHERE
PageIndex.IndexID > @PageLowerBound
AND O.[WorkNo] = PageIndex.[WorkNo]
ORDER BY
PageIndex.IndexID

-- get row count
SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
SET @SQL = @SQL + ' FROM dbo.[dl_admin]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
exec sp_executesql @SQL

END 自己可以完善下 可以改得更加灵活点 比如说排序字段 排序类型 表名等等 看你的要求了