sql200
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [ dbo ] . [ PROCE_PageView2000 ]
(
@tbname nvarchar ( 100 ), -- 要分页显示的表名
@FieldKey nvarchar ( 1000 ), -- 用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int = 1 , -- 要显示的页码
@PageSize int = 10 , -- 每页的大小(记录数)
@FieldShow nvarchar ( 1000 ) = '' , -- 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar ( 1000 ) = '' , -- 以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@WhereString nvarchar ( 1000 ) = N '' , -- 查询条件
@RecordCount 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 ( @WhereString ,N '' ) = N ''
SET @WhereString = N ''
ELSE
SET @WhereString = N ' WHERE ( ' + @WhereString + N ' ) '
-- 如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值)
IF @RecordCount IS NULL
BEGIN
DECLARE @sql nvarchar ( 4000 )
SET @sql = N ' SELECT @RecordCount=COUNT(*) '
+ N ' FROM ' + @tbname
+ N ' ' + @WhereString
EXEC sp_executesql @sql ,N ' @RecordCount int OUTPUT ' , @RecordCount OUTPUT
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 ' ' + @WhereString
+ 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 ' ,
@WhereString = REPLACE ( @WhereString , @Field ,N ' a. ' + @Field ),
@FieldOrder = REPLACE ( @FieldOrder , @Field ,N ' a. ' + @Field ),
@FieldShow = REPLACE ( @FieldShow , @Field ,N ' a. ' + @Field )
SELECT @WhereString = REPLACE ( @WhereString , @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 @WhereString = '' THEN N ' WHERE ( '
ELSE @WhereString + 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 ' + @WhereString
+ N ' ' + @FieldOrder
+ N ' )b ON ' + @Where1
+ N ' ' + @Where2
+ N ' ' + @FieldOrder )
END
sql2005
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [ dbo ] . [ PROCE_SQL2005PAGECHANGE ]
(
@TableName varchar ( 50 ), -- 表名
@ReFieldsStr varchar ( 200 ) = ' * ' , -- 字段名(全部字段为*)
@OrderString varchar ( 200 ), -- 排序字段(必须!支持多字段不用加order by)
@WhereString varchar ( 500 ) = N '' , -- 条件语句(不用加where)
@PageSize int , -- 每页多少条记录
@PageIndex int = 1 , -- 指定当前为第几页
@TotalRecord int output -- 返回总记录数
)
AS
BEGIN
-- 处理开始点和结束点
Declare @StartRecord int ;
Declare @EndRecord int ;
Declare @TotalCountSql nvarchar ( 500 );
Declare @SqlString nvarchar ( 2000 );
set @StartRecord = ( @PageIndex - 1 ) * @PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
SET @TotalCountSql = N ' select @TotalRecord = count(*) from ' + @TableName ; -- 总记录数语句
SET @SqlString = N ' (select row_number() over (order by ' + @OrderString + ' ) as rowId, ' + @ReFieldsStr + ' from ' + @TableName ; -- 查询语句
--
IF ( @WhereString ! = '' or @WhereString != null )
BEGIN
SET @TotalCountSql = @TotalCountSql + ' where ' + @WhereString ;
SET @SqlString = @SqlString + ' where ' + @WhereString ;
END
-- 第一次执行得到
-- IF(@TotalRecord is null)
-- BEGIN
EXEC sp_executesql @totalCountSql ,N ' @TotalRecord int out ' , @TotalRecord output; -- 返回总记录数
-- END
-- --执行主语句
set @SqlString = ' select * from ' + @SqlString + ' ) as t where rowId between ' + ltrim ( str ( @StartRecord )) + ' and ' + ltrim ( str ( @EndRecord ));
Exec ( @SqlString )
END