从别的地方看到的,我加了几句注释:
SET
QUOTED_IDENTIFIER
ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [ dbo ] . [ Common_Pagination ]
@tablename varchar ( 100 ), -- 要调用的表名
@tablefield varchar ( 20 ), -- 排序关键字段
@where varchar ( 5000 ), -- 判断语句(如pid=1--分类id为1的下属内容)
@orderby varchar ( 500 ), -- 排序方法(如order by id desc)
@fieldlist varchar ( 1000 ), -- 要调用的表中的字段列表
@curpage int , -- 当前页码(从0开始计)
@page_record int , -- 要调用的分页记录数
@sort varchar ( 8 ) -- 判断分页内容的排序方向,desc ,asc,注意这个值应该与上面的order by方向一致
AS
BEGIN
DECLARE @cmd varchar ( 8000 )
DECLARE @uprecord int
DECLARE @Op varchar ( 2 ) -- 操作符
DECLARE @max_min varchar ( 4 ) -- 最大/最小计算
SET @op = ' < '
SET @max_min = ' MIN '
IF @sort = ' asc '
BEGIN
SET @Op = ' > '
SET @max_min = ' MAX '
END
SET @uprecord = @curpage * @page_record
SET @where = ' ( ' + @where + ' ) '
IF @curpage = 0
SET @cmd = ' SELECT TOP ' + cast ( @page_record AS NVARCHAR ) + ' ' + @fieldlist + ' FROM ' + @tablename + ' WHERE ' + @where + ' ' + @orderby
ELSE
SET @cmd = ' SELECT TOP ' + cast ( @page_record AS NVARCHAR ) + ' ' + @fieldlist + ' FROM ' + @tablename + ' WHERE ' + @where + ' AND ' + @tablefield + '
' + @op + ' (SELECT ' + @max_min + ' ( ' + @tablefield + ' ) FROM (SELECT TOP ' + cast ( @uprecord AS NVARCHAR ) + ' ' + @tablefield + ' FROM ' + @tablename + ' WHERE
' + @where + ' ' + @orderby + ' ) AS TmpTbl ) AND ' + @where + ' ' + @orderby
EXEC ( @cmd )
PRINT ( @cmd )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [ dbo ] . [ Common_Pagination ]
@tablename varchar ( 100 ), -- 要调用的表名
@tablefield varchar ( 20 ), -- 排序关键字段
@where varchar ( 5000 ), -- 判断语句(如pid=1--分类id为1的下属内容)
@orderby varchar ( 500 ), -- 排序方法(如order by id desc)
@fieldlist varchar ( 1000 ), -- 要调用的表中的字段列表
@curpage int , -- 当前页码(从0开始计)
@page_record int , -- 要调用的分页记录数
@sort varchar ( 8 ) -- 判断分页内容的排序方向,desc ,asc,注意这个值应该与上面的order by方向一致
AS
BEGIN
DECLARE @cmd varchar ( 8000 )
DECLARE @uprecord int
DECLARE @Op varchar ( 2 ) -- 操作符
DECLARE @max_min varchar ( 4 ) -- 最大/最小计算
SET @op = ' < '
SET @max_min = ' MIN '
IF @sort = ' asc '
BEGIN
SET @Op = ' > '
SET @max_min = ' MAX '
END
SET @uprecord = @curpage * @page_record
SET @where = ' ( ' + @where + ' ) '
IF @curpage = 0
SET @cmd = ' SELECT TOP ' + cast ( @page_record AS NVARCHAR ) + ' ' + @fieldlist + ' FROM ' + @tablename + ' WHERE ' + @where + ' ' + @orderby
ELSE
SET @cmd = ' SELECT TOP ' + cast ( @page_record AS NVARCHAR ) + ' ' + @fieldlist + ' FROM ' + @tablename + ' WHERE ' + @where + ' AND ' + @tablefield + '
' + @op + ' (SELECT ' + @max_min + ' ( ' + @tablefield + ' ) FROM (SELECT TOP ' + cast ( @uprecord AS NVARCHAR ) + ' ' + @tablefield + ' FROM ' + @tablename + ' WHERE
' + @where + ' ' + @orderby + ' ) AS TmpTbl ) AND ' + @where + ' ' + @orderby
EXEC ( @cmd )
PRINT ( @cmd )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO