利用Top n实现的分页存储过程
2009-11-13 00:00:00 来源:WEB开发网存储过程为:
-- =============================================
-- Author: <李培>
-- Create date: <2009-11-11>
-- Description: <利用Top n实现的分页存储过程>
-- =============================================
CREATE PROCEDURE [dbo].[CommonPager]
@strTableName sysname, --必填:进行分页的表名
@strKeyField nvarchar(1000) = N'', --必填:主键或唯一键(数值类型),推荐添加索引
@intPageNumber int = 1, --选填:显示的页码,默认值为 1
@intPageSize int = 10, --选填:显示的页面大小,默认值为 10
@strShowFields nvarchar(1000) = N'', --选填:显示的字段,默认为 全部字段
@strWhereFilter nvarchar(1000) = N'', --选填:Where 条件句
@strOrderFields nvarchar(1000) = N'', --选填:排序字段列表,以逗号分隔
@intPageCount int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- ******************参数检查及处理:开始******************
-- 表名
IF OBJECT_ID(@strTableName) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@strTableName)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@strTableName),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@strTableName),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@strTableName),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@strTableName)
RETURN
END
-- 主键字段
IF ISNULL(@strKeyField,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键或唯一键(数值类型)',1,16)
RETURN
END
-- 显示字段
IF ISNULL(@strShowFields,N'')=N''
SET @strShowFields=N'*'
-- Where 条件句
IF ISNULL(@strWhereFilter,N'') != N''
SET @strWhereFilter = N' AND ' + @strWhereFilter + N' '
-- 排序语句
IF ISNULL(@strOrderFields,N'') != N''
SET @strOrderFields = N' ORDER BY ' + @strOrderFields + N' '
-- 页码
IF ISNULL(@intPageNumber,0)<1
SET @intPageNumber=1
-- 页大小
IF ISNULL(@intPageSize,0)<1
SET @intPageSize=10
-- ******************参数检查及处理:结束******************
-- 计算总页数
DECLARE @selectItemsCount nvarchar(4000)
SET @SelectItemsCount = N'SELECT @intPageCount = COUNT( ' + @strKeyField + N' ) FROM ' + @strTableName + N' WHERE 1=1 ' + @strWhereFilter
EXEC sp_executesql @selectItemsCount , N'@intPageCount int OUTPUT,@strKeyField nvarchar(1000),@strTableName nvarchar(1000),@strWhereFilter nvarchar(1000)'
,@intPageCount OUTPUT,@strKeyField,@strTableName,@strWhereFilter
SET @intPageCount = (@intPageCount + @intPageSize -1) / @intPageSize
-- 计算
DECLARE @TopNForMain nvarchar(50)
DECLARE @TopNForChild nvarchar(50)
SET @TopNForMain = @intPageSize
SET @TopNForChild = @intPageSize * (@intPageNumber - 1)
-- 返回数据集
IF @intPageNumber = 1
BEGIN
DECLARE @queryStr1 nvarchar(4000)
SET @queryStr1 = N'SELECT TOP '+ @TopNForMain + N' ' + @strShowFields + N' FROM ' + @strTableName + N' WHERE 1=1 ' + @strWhereFilter + N' ' + @strOrderFields
EXEC sp_executesql @queryStr1
,N'@TopNForMain nvarchar(50),@strShowFields nvarchar(1000),@strTableName sysname,@strWhereFilter nvarchar(1000),@strOrderFields nvarchar(1000)'
,@TopNForMain,@strWhereFilter,@strTableName,@strShowFields,@strOrderFields
PRINT @queryStr1
END
ELSE
BEGIN
DECLARE @queryStr2 nvarchar(4000)
SET @queryStr2 = N'SELECT TOP ' + @TopNForMain + N' ' + @strShowFields + N' FROM ' + @strTableName + N' WHERE ' + @strKeyField
+ N' NOT IN ( SELECT TOP ' + @TopNForChild + N' ' + @strKeyField + N' FROM ' + @strTableName + N' WHERE 1=1 ' + @strWhereFilter + N' ' + @strOrderFields + N' )'
+ @strWhereFilter + @strOrderFields
EXEC sp_executesql @queryStr2
,N'@TopNForMain nvarchar(50),@strShowFields nvarchar(1000),@strTableName sysname,@strKeyField nvarchar(1000),@TopNForChild nvarchar(50),@strWhereFilter nvarchar(1000),@strOrderFields nvarchar(1000)'
,@TopNForMain,@strShowFields,@strTableName,@strKeyField,@TopNForChild,@strWhereFilter,@strOrderFields
PRINT @queryStr2
END
END
赞助商链接