--
TOP n 实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar ( 4000 )
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = ( @PageCurrent - 1 ) * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
-- 处理别名
IF @FieldShow = N ' * '
SET @FieldShow = N ' a.* '
-- 生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar ( 4000 ), @Where2 nvarchar ( 4000 ),
@s nvarchar ( 1000 ), @Field sysname
SELECT @Where1 = N '' , @Where2 = N '' , @s = @FieldKey
WHILE CHARINDEX (N ' , ' , @s ) > 0
SELECT @Field = LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 ),
@s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),
@Where1 = @Where1 + N ' AND a. ' + @Field + N ' =b. ' + @Field ,
@Where2 = @Where2 + N ' AND b. ' + @Field + N ' IS NULL ' ,
@Where = REPLACE ( @Where , @Field ,N ' a. ' + @Field ),
@FieldOrder = REPLACE ( @FieldOrder , @Field ,N ' a. ' + @Field ),
@FieldShow = REPLACE ( @FieldShow , @Field ,N ' a. ' + @Field )
SELECT @Where = REPLACE ( @Where , @s ,N ' a. ' + @s ),
@FieldOrder = REPLACE ( @FieldOrder , @s ,N ' a. ' + @s ),
@FieldShow = REPLACE ( @FieldShow , @s ,N ' a. ' + @s ),
@Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' =b. ' + @s , 1 , 5 ,N '' ),
@Where2 = CASE
WHEN @Where = '' THEN N ' WHERE ( '
ELSE @Where + N ' AND ( '
END + N ' b. ' + @s + N ' IS NULL ' + @Where2 + N ' ) '
-- 执行查询
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' a LEFT JOIN(SELECT TOP ' + @TopN1
+ N ' ' + @FieldKey
+ N ' FROM ' + @tbname
+ N ' a ' + @Where
+ N ' ' + @FieldOrder
+ N ' )b ON ' + @Where1
+ N ' ' + @Where2
+ N ' ' + @FieldOrder )
END
CREATE PROC sp_PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar ( 4000 )
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = ( @PageCurrent - 1 ) * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
-- 处理别名
IF @FieldShow = N ' * '
SET @FieldShow = N ' a.* '
-- 生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar ( 4000 ), @Where2 nvarchar ( 4000 ),
@s nvarchar ( 1000 ), @Field sysname
SELECT @Where1 = N '' , @Where2 = N '' , @s = @FieldKey
WHILE CHARINDEX (N ' , ' , @s ) > 0
SELECT @Field = LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 ),
@s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),
@Where1 = @Where1 + N ' AND a. ' + @Field + N ' =b. ' + @Field ,
@Where2 = @Where2 + N ' AND b. ' + @Field + N ' IS NULL ' ,
@Where = REPLACE ( @Where , @Field ,N ' a. ' + @Field ),
@FieldOrder = REPLACE ( @FieldOrder , @Field ,N ' a. ' + @Field ),
@FieldShow = REPLACE ( @FieldShow , @Field ,N ' a. ' + @Field )
SELECT @Where = REPLACE ( @Where , @s ,N ' a. ' + @s ),
@FieldOrder = REPLACE ( @FieldOrder , @s ,N ' a. ' + @s ),
@FieldShow = REPLACE ( @FieldShow , @s ,N ' a. ' + @s ),
@Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' =b. ' + @s , 1 , 5 ,N '' ),
@Where2 = CASE
WHEN @Where = '' THEN N ' WHERE ( '
ELSE @Where + N ' AND ( '
END + N ' b. ' + @s + N ' IS NULL ' + @Where2 + N ' ) '
-- 执行查询
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' a LEFT JOIN(SELECT TOP ' + @TopN1
+ N ' ' + @FieldKey
+ N ' FROM ' + @tbname
+ N ' a ' + @Where
+ N ' ' + @FieldOrder
+ N ' )b ON ' + @Where1
+ N ' ' + @Where2
+ N ' ' + @FieldOrder )
END
--
临时表缓存实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar ( 4000 )
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = @PageCurrent * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
-- 生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar ( 4000 ), @s nvarchar ( 1000 )
SELECT @Where1 = N '' , @s = @FieldKey
WHILE CHARINDEX (N ' , ' , @s ) > 0
SELECT @s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),
@Where1 = @Where1
+ N ' AND a. ' + LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 )
+ N ' = ' + LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 )
SELECT @Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' = ' + @s , 1 , 5 ,N '' ),
@TopN = @TopN1 - @PageSize
-- 执行查询
EXEC (N ' SET ROWCOUNT ' + @TopN1
+ N ' SELECT ' + @FieldKey
+ N ' INTO # FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
+ N ' SET ROWCOUNT ' + @TopN
+ N ' DELETE FROM # '
+ N ' SELECT ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' a WHERE EXISTS(SELECT * FROM # WHERE ' + @Where1
+ N ' ) ' + @FieldOrder )
END
CREATE PROC sp_PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar ( 4000 )
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = @PageCurrent * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
-- 生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar ( 4000 ), @s nvarchar ( 1000 )
SELECT @Where1 = N '' , @s = @FieldKey
WHILE CHARINDEX (N ' , ' , @s ) > 0
SELECT @s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),
@Where1 = @Where1
+ N ' AND a. ' + LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 )
+ N ' = ' + LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 )
SELECT @Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' = ' + @s , 1 , 5 ,N '' ),
@TopN = @TopN1 - @PageSize
-- 执行查询
EXEC (N ' SET ROWCOUNT ' + @TopN1
+ N ' SELECT ' + @FieldKey
+ N ' INTO # FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
+ N ' SET ROWCOUNT ' + @TopN
+ N ' DELETE FROM # '
+ N ' SELECT ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' a WHERE EXISTS(SELECT * FROM # WHERE ' + @Where1
+ N ' ) ' + @FieldOrder )
END
--
字符串缓存实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey sysname, -- 用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
DECLARE @sql nvarchar ( 4000 )
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = @PageCurrent * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
SELECT @PageCurrent = @TopN1 ,
@sql = N ' SELECT @n=@n-1,@s=CASE WHEN @n< ' + @TopN
+ N ' THEN @s+N '' , '' +QUOTENAME(RTRIM(CAST( ' + @FieldKey
+ N ' as varchar(8000))),N '''''''' ) ELSE N '''' END FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql ,
N ' @n int,@s nvarchar(4000) OUTPUT ' ,
@PageCurrent , @sql OUTPUT
SET ROWCOUNT 0
IF @sql = N ''
EXEC (N ' SELECT TOP 0 '
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname )
ELSE
BEGIN
SET @sql = STUFF ( @sql , 1 , 1 ,N '' )
-- 执行查询
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' WHERE ' + @FieldKey
+ N ' IN( ' + @sql
+ N ' ) ' + @FieldOrder )
END
END
CREATE PROC sp_PageView
@tbname sysname, -- 要分页显示的表名
@FieldKey sysname, -- 用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar ( 1000 ) = '' , -- 查询条件
@PageCount int OUTPUT -- 总页数
AS
DECLARE @sql nvarchar ( 4000 )
SET NOCOUNT ON
-- 检查对象是否有效
IF OBJECT_ID ( @tbname ) IS NULL
BEGIN
RAISERROR (N ' 对象"%s"不存在 ' , 1 , 16 , @tbname )
RETURN
END
IF OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTable ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsView ' ) = 0
AND OBJECTPROPERTY ( OBJECT_ID ( @tbname ),N ' IsTableFunction ' ) = 0
BEGIN
RAISERROR (N ' "%s"不是表、视图或者表值函数 ' , 1 , 16 , @tbname )
RETURN
END
-- 分页字段检查
IF ISNULL ( @FieldKey ,N '' ) = ''
BEGIN
RAISERROR (N ' 分页处理需要主键(或者惟一键) ' , 1 , 16 )
RETURN
END
-- 其他参数检查及规范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
SET @FieldOrder = N ''
ELSE
SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
SET @Where = N ''
ELSE
SET @Where = N ' WHERE ( ' + @Where + N ' ) '
-- 如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql = N ' SELECT @PageCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @Where
EXEC sp_executesql @sql ,N ' @PageCount int OUTPUT ' , @PageCount OUTPUT
SET @PageCount = ( @PageCount + @PageSize - 1 ) / @PageSize
END
-- 计算分页显示的TOPN值
DECLARE @TopN varchar ( 20 ), @TopN1 varchar ( 20 )
SELECT @TopN = @PageSize ,
@TopN1 = @PageCurrent * @PageSize
-- 第一页直接显示
IF @PageCurrent = 1
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
SELECT @PageCurrent = @TopN1 ,
@sql = N ' SELECT @n=@n-1,@s=CASE WHEN @n< ' + @TopN
+ N ' THEN @s+N '' , '' +QUOTENAME(RTRIM(CAST( ' + @FieldKey
+ N ' as varchar(8000))),N '''''''' ) ELSE N '''' END FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql ,
N ' @n int,@s nvarchar(4000) OUTPUT ' ,
@PageCurrent , @sql OUTPUT
SET ROWCOUNT 0
IF @sql = N ''
EXEC (N ' SELECT TOP 0 '
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname )
ELSE
BEGIN
SET @sql = STUFF ( @sql , 1 , 1 ,N '' )
-- 执行查询
EXEC (N ' SELECT TOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' WHERE ' + @FieldKey
+ N ' IN( ' + @sql
+ N ' ) ' + @FieldOrder )
END
END
--
使用系统存储过程实现的通用分页存储过程(邹建)
CREATE PROC sp_PageView
@sql ntext, -- 要执行的sql语句
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小
@PageCount int OUTPUT -- 总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
-- 初始化分页游标
EXEC sp_cursoropen
@cursor = @p1 OUTPUT,
@stmt = @sql,
@scrollopt = 1 ,
@ccopt = 1 ,
@rowcount = @PageCount OUTPUT
-- 计算总页数
IF ISNULL(@PageSize, 0 ) < 1
SET @PageSize = 10
SET @PageCount = (@PageCount + @PageSize - 1 ) / @PageSize
IF ISNULL(@PageCurrent, 0 ) < 1 OR ISNULL(@PageCurrent, 0 ) > @PageCount
SET @PageCurrent = 1
ELSE
SET @PageCurrent = (@PageCurrent - 1 ) * @PageSize + 1
-- 显示指定页的数据
EXEC sp_cursorfetch @p1, 16 ,@PageCurrent,@PageSize
-- 关闭分页游标
EXEC sp_cursorclose @p1
CREATE PROC sp_PageView
@sql ntext, -- 要执行的sql语句
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小
@PageCount int OUTPUT -- 总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
-- 初始化分页游标
EXEC sp_cursoropen
@cursor = @p1 OUTPUT,
@stmt = @sql,
@scrollopt = 1 ,
@ccopt = 1 ,
@rowcount = @PageCount OUTPUT
-- 计算总页数
IF ISNULL(@PageSize, 0 ) < 1
SET @PageSize = 10
SET @PageCount = (@PageCount + @PageSize - 1 ) / @PageSize
IF ISNULL(@PageCurrent, 0 ) < 1 OR ISNULL(@PageCurrent, 0 ) > @PageCount
SET @PageCurrent = 1
ELSE
SET @PageCurrent = (@PageCurrent - 1 ) * @PageSize + 1
-- 显示指定页的数据
EXEC sp_cursorfetch @p1, 16 ,@PageCurrent,@PageSize
-- 关闭分页游标
EXEC sp_cursorclose @p1
--
根据分类表实现的分页存储过程(邹建)
-- 要分页的原始数据
CREATE TABLE tb(
ID int PRIMARY KEY, -- 记录编号
grade varchar( 10 ), -- 类别名称
uptime datetime) -- 更新时间
INSERT tb SELECT 1 , ' a ' , ' 2004-12-11 '
UNION ALL SELECT 2 , ' b ' , ' 2004-12-11 '
UNION ALL SELECT 3 , ' c ' , ' 2004-12-11 '
UNION ALL SELECT 4 , ' a ' , ' 2004-12-12 '
UNION ALL SELECT 5 , ' c ' , ' 2004-12-13 '
UNION ALL SELECT 6 , ' c ' , ' 2004-12-13 '
UNION ALL SELECT 7 , ' a ' , ' 2004-12-14 '
UNION ALL SELECT 8 , ' a ' , ' 2004-12-15 '
UNION ALL SELECT 9 , ' b ' , ' 2004-12-16 '
UNION ALL SELECT 10 , ' b ' , ' 2004-12-17 '
UNION ALL SELECT 11 , ' a ' , ' 2004-12-17 '
-- 分页定义表
CREATE TABLE tb_Page(
grade varchar( 10 ) PRIMARY KEY, -- 类别名称,与tb表的grade关联
Records int , -- 每页显示的记录数
Orders int ) -- 在页中的显示顺序
INSERT tb_Page SELECT ' c ' , 2 , 1
UNION ALL SELECT ' b ' , 1 , 2
UNION ALL SELECT ' a ' , 2 , 3
GO
-- 实现分页处理的存储过程
CREATE PROC p_PageView
@PageCurrent int = 1 -- 要显示的当前页码
AS
SET NOCOUNT ON
-- 得到每页的记录数
DECLARE @PageSize int
SELECT @PageSize = SUM(Records) FROM tb_Page
IF ISNULL(@PageSize, 0 ) < 0 RETURN
-- 分页显示处理
SET @PageCurrent = @PageCurrent * @PageSize
SET ROWCOUNT @PageCurrent
SELECT SID = IDENTITY( int , 1 , 1 ),ID
INTO # FROM(
SELECT TOP 100 PERCENT a.ID
FROM tb a
LEFT JOIN tb_Page b ON a.grade = b.grade
ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END, -- 分类没有定义的显示在最后
((SELECT COUNT( * ) FROM tb
WHERE grade = a.grade
AND (uptime > a.uptime OR uptime = a.uptime AND id >= a.id)) - 1 )
/ b.Records,
b.Orders,a.ID DESC)a
IF @PageCurrent > @PageSize
BEGIN
SET @PageCurrent = @PageCurrent - @PageSize
SET ROWCOUNT @PageCurrent
DELETE FROM #
END
SELECT a. * FROM tb a,# b
WHERE a.ID = b.ID
ORDER BY b.SID
GO
-- 调用
EXEC p_PageView 2
/**/ /*--结果
ID grade uptime
----------- ---------- ------------------------------------------------------
3 c 2004-12-11 00:00:00.000
9 b 2004-12-16 00:00:00.000
7 a 2004-12-14 00:00:00.000
4 a 2004-12-12 00:00:00.000
2 b 2004-12-11 00:00:00.000
--*/
-- 要分页的原始数据
CREATE TABLE tb(
ID int PRIMARY KEY, -- 记录编号
grade varchar( 10 ), -- 类别名称
uptime datetime) -- 更新时间
INSERT tb SELECT 1 , ' a ' , ' 2004-12-11 '
UNION ALL SELECT 2 , ' b ' , ' 2004-12-11 '
UNION ALL SELECT 3 , ' c ' , ' 2004-12-11 '
UNION ALL SELECT 4 , ' a ' , ' 2004-12-12 '
UNION ALL SELECT 5 , ' c ' , ' 2004-12-13 '
UNION ALL SELECT 6 , ' c ' , ' 2004-12-13 '
UNION ALL SELECT 7 , ' a ' , ' 2004-12-14 '
UNION ALL SELECT 8 , ' a ' , ' 2004-12-15 '
UNION ALL SELECT 9 , ' b ' , ' 2004-12-16 '
UNION ALL SELECT 10 , ' b ' , ' 2004-12-17 '
UNION ALL SELECT 11 , ' a ' , ' 2004-12-17 '
-- 分页定义表
CREATE TABLE tb_Page(
grade varchar( 10 ) PRIMARY KEY, -- 类别名称,与tb表的grade关联
Records int , -- 每页显示的记录数
Orders int ) -- 在页中的显示顺序
INSERT tb_Page SELECT ' c ' , 2 , 1
UNION ALL SELECT ' b ' , 1 , 2
UNION ALL SELECT ' a ' , 2 , 3
GO
-- 实现分页处理的存储过程
CREATE PROC p_PageView
@PageCurrent int = 1 -- 要显示的当前页码
AS
SET NOCOUNT ON
-- 得到每页的记录数
DECLARE @PageSize int
SELECT @PageSize = SUM(Records) FROM tb_Page
IF ISNULL(@PageSize, 0 ) < 0 RETURN
-- 分页显示处理
SET @PageCurrent = @PageCurrent * @PageSize
SET ROWCOUNT @PageCurrent
SELECT SID = IDENTITY( int , 1 , 1 ),ID
INTO # FROM(
SELECT TOP 100 PERCENT a.ID
FROM tb a
LEFT JOIN tb_Page b ON a.grade = b.grade
ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END, -- 分类没有定义的显示在最后
((SELECT COUNT( * ) FROM tb
WHERE grade = a.grade
AND (uptime > a.uptime OR uptime = a.uptime AND id >= a.id)) - 1 )
/ b.Records,
b.Orders,a.ID DESC)a
IF @PageCurrent > @PageSize
BEGIN
SET @PageCurrent = @PageCurrent - @PageSize
SET ROWCOUNT @PageCurrent
DELETE FROM #
END
SELECT a. * FROM tb a,# b
WHERE a.ID = b.ID
ORDER BY b.SID
GO
-- 调用
EXEC p_PageView 2
/**/ /*--结果
ID grade uptime
----------- ---------- ------------------------------------------------------
3 c 2004-12-11 00:00:00.000
9 b 2004-12-16 00:00:00.000
7 a 2004-12-14 00:00:00.000
4 a 2004-12-12 00:00:00.000
2 b 2004-12-11 00:00:00.000
--*/
还有一个,论坛里找的,不是老大的:
CREATE
PROCEDURE
SP_Page
@TB VARCHAR ( 50 ),
@COL VARCHAR ( 50 ),
-- @COLTYPE INT,
@ORDERBY BIT ,
@COLLIST VARCHAR ( 800 ),
@PAGESIZE INT ,
@PAGE INT ,
@CONDITION VARCHAR ( 800 ),
@RecPages INT ,
@RecCount INT OUTPUT,
@PAGES INT OUTPUT,
@OUTSQL NVARCHAR ( 4000 ) OUTPUT
AS
DECLARE @SQL NVARCHAR ( 4000 )
DECLARE @WHERE1 VARCHAR ( 800 )
DECLARE @WHERE2 VARCHAR ( 800 )
IF @CONDITION IS NULL OR RTRIM ( @CONDITION ) = ''
BEGIN
SET @WHERE1 = ' WHERE '
SET @WHERE2 = ' '
END
ELSE
BEGIN
SET @WHERE1 = ' WHERE ( ' + @CONDITION + ' ) AND '
SET @WHERE2 = ' WHERE ( ' + @CONDITION + ' ) '
END
SET @SQL = ' SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/ ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ) FROM ' + @TB + @WHERE2
IF @RecPages = 0
EXEC SP_EXECUTESQL @SQL ,N ' @PAGES INT OUTPUT,@RecCount INT OUTPUT ' , @PAGES OUTPUT, @RecCount OUTPUT
ELSE
SELECT @PAGES = @RecPages
IF @ORDERBY = 0
SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE1 + @COL + ' >(SELECT MAX( ' + @COL + ' ) ' + ' FROM (SELECT TOP ' + CAST ( @PAGESIZE * ( @PAGE - 1 ) AS VARCHAR ) + ' ' +
@COL + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + ' ) t) ORDER BY ' + @COL
ELSE
SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE1 + @COL + ' <(SELECT MIN( ' + @COL + ' ) ' + ' FROM (SELECT TOP ' + CAST ( @PAGESIZE * ( @PAGE - 1 ) AS VARCHAR ) + ' ' +
@COL + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + ' DESC) t) ORDER BY ' + @COL + ' DESC '
IF @PAGE = 1
SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC ' END
SET @OUTSQL = @SQL
EXEC ( @SQL )
GO
@TB VARCHAR ( 50 ),
@COL VARCHAR ( 50 ),
-- @COLTYPE INT,
@ORDERBY BIT ,
@COLLIST VARCHAR ( 800 ),
@PAGESIZE INT ,
@PAGE INT ,
@CONDITION VARCHAR ( 800 ),
@RecPages INT ,
@RecCount INT OUTPUT,
@PAGES INT OUTPUT,
@OUTSQL NVARCHAR ( 4000 ) OUTPUT
AS
DECLARE @SQL NVARCHAR ( 4000 )
DECLARE @WHERE1 VARCHAR ( 800 )
DECLARE @WHERE2 VARCHAR ( 800 )
IF @CONDITION IS NULL OR RTRIM ( @CONDITION ) = ''
BEGIN
SET @WHERE1 = ' WHERE '
SET @WHERE2 = ' '
END
ELSE
BEGIN
SET @WHERE1 = ' WHERE ( ' + @CONDITION + ' ) AND '
SET @WHERE2 = ' WHERE ( ' + @CONDITION + ' ) '
END
SET @SQL = ' SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/ ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ) FROM ' + @TB + @WHERE2
IF @RecPages = 0
EXEC SP_EXECUTESQL @SQL ,N ' @PAGES INT OUTPUT,@RecCount INT OUTPUT ' , @PAGES OUTPUT, @RecCount OUTPUT
ELSE
SELECT @PAGES = @RecPages
IF @ORDERBY = 0
SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE1 + @COL + ' >(SELECT MAX( ' + @COL + ' ) ' + ' FROM (SELECT TOP ' + CAST ( @PAGESIZE * ( @PAGE - 1 ) AS VARCHAR ) + ' ' +
@COL + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + ' ) t) ORDER BY ' + @COL
ELSE
SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE1 + @COL + ' <(SELECT MIN( ' + @COL + ' ) ' + ' FROM (SELECT TOP ' + CAST ( @PAGESIZE * ( @PAGE - 1 ) AS VARCHAR ) + ' ' +
@COL + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + ' DESC) t) ORDER BY ' + @COL + ' DESC '
IF @PAGE = 1
SET @SQL = ' SELECT TOP ' + CAST ( @PAGESIZE AS VARCHAR ) + ' ' + @COLLIST + ' FROM ' + @TB + @WHERE2 + ' ORDER BY ' + @COL + CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC ' END
SET @OUTSQL = @SQL
EXEC ( @SQL )
GO