自己整理的一些分页存储过程

/**/ /**/ /**/ /*
说明：1.支持多表查询2.支持任意排序3.不支持表别名
参考了
evafly920:[分享]千万数量级分页存储过程（效果演示）
地址：http://blog.youkuaiyun.com/evafly920/archive/2006/03/03/614813.aspx

IF(EXISTS(SELECT*FROMsysobjectsWHERE[id]=OBJECT_ID('usp_PagingLarge')ANDxtype='P'))
DROPPROCEDUREusp_PagingLarge
*/

GO

CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR ( 200 ), -- 表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR ( 100 ), -- 主键，可以为空，但@Order为空时该值不能为空
@Fields VARCHAR ( 200 ), -- 要取出的字段，可以是多个表的字段，可以为空，为空表示select*
@PageSize INT , -- 每页记录数
@CurrentPage INT , -- 当前页，0表示第1页
@Filter VARCHAR ( 200 ) = '' , -- 条件，可以为空，不用填where
@Group VARCHAR ( 200 ) = '' , -- 分组依据，可以为空，不用填groupby
@Order VARCHAR ( 200 ) = '' -- 排序，可以为空，为空默认按主键升序排列，不用填orderby
AS
BEGIN
DECLARE @SortColumn VARCHAR ( 200 )
DECLARE @Operator CHAR ( 2 )
DECLARE @SortTable VARCHAR ( 200 )
DECLARE @SortName VARCHAR ( 200 )
IF @Fields = ''
SET @Fields = ' * '
IF @Filter = ''
SET @Filter = ' WHERE1=1 '
ELSE
SET @Filter = ' WHERE ' + @Filter
IF @Group <> ''
SET @Group = ' GROUPBY ' + @Group

IF @Order <> ''
BEGIN
DECLARE @pos1 INT , @pos2 INT
SET @Order = REPLACE ( REPLACE ( @Order , ' asc ' , ' ASC ' ), ' desc ' , ' DESC ' )
IF CHARINDEX ( ' DESC ' , @Order ) > 0
IF CHARINDEX ( ' ASC ' , @Order ) > 0
BEGIN
IF CHARINDEX ( ' DESC ' , @Order ) < CHARINDEX ( ' ASC ' , @Order )
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
END
ELSE
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
SET @SortColumn = REPLACE ( REPLACE ( REPLACE ( @Order , ' ASC ' , '' ), ' DESC ' , '' ), ' ' , '' )
SET @pos1 = CHARINDEX ( ' , ' , @SortColumn )
IF @pos1 > 0
SET @SortColumn = SUBSTRING ( @SortColumn , 1 , @pos1 - 1 )
SET @pos2 = CHARINDEX ( ' . ' , @SortColumn )
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING ( @SortColumn , 1 , @pos2 - 1 )
IF @pos1 > 0
SET @SortName = SUBSTRING ( @SortColumn , @pos2 + 1 , @pos1 - @pos2 - 1 )
ELSE
SET @SortName = SUBSTRING ( @SortColumn , @pos2 + 1 , LEN ( @SortColumn ) - @pos2 )
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = ' >= '
END

DECLARE @type varchar ( 50 )
DECLARE @prec int
SELECT @type = t.name, @prec = c.prec
FROM sysobjectso
JOIN syscolumnsc on o.id = c.id
JOIN systypest on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '

DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC ( '
DECLARE@SortColumnBegin ' + @type + '
SETROWCOUNT ' + @TopRows + '
SELECT@SortColumnBegin= ' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDERBY ' + @Order + '
SETROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + ' @SortColumnBegin ' + @Group + ' ORDERBY ' + @Order + '
' )
END

GO

-- 调用例子：
-- 1.单表/单排序
EXEC usp_PagingLarge ' bigtable ' , ' d_id ' , ' d_id,d_title,d_content,d_time ' , 20 , 1 , '' , '' , ' d_iddesc '
-- 2.单表/多排序
EXEC usp_PagingLarge ' bigtable ' , ' d_id ' , ' * ' , 20 , 0 , '' , '' , ' d_timeasc,d_iddesc '
-- 3.多表/单排序
EXEC usp_PagingLarge ' bigtableleftjoinbigtable_authoronbigtable.d_id=bigtable_author.BigTable_id ' , ' bigtable.d_id ' , ' bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ' , 20 , 0 , '' , '' , ' bigtable.d_idasc '
-- 4.多表/多排序
EXEC usp_PagingLarge ' bigtableleftjoinbigtable_authoronbigtable.d_id=bigtable_author.BigTable_id ' , ' bigtable.d_id ' , ' bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author ' , 20 , 0 , '' , '' , ' bigtable.d_timeasc,bigtable.d_iddesc '

与自定义分页结合例子：
/Files/jiny-z/Paging_Custom.rar

alter PROCEDURE SP_Pagination
/**/ /*
***************************************************************
**分页存储过程**
***************************************************************
参数说明:
1.Tables:表名称,视图
2.PrimaryKey:主关键字
3.Sort:排序语句，不带OrderBy比如：NewsIDDesc,OrderRowsAsc
4.CurrentPage:当前页码
5.PageSize:分页尺寸
6.Fields:字段
6.Filter:过滤语句，不带Where
7.Group:Group语句,不带GroupBy
8.docount：1返回总行数，0返回列表

***************************************************************/
(
@Tables varchar ( 1000 ),
@PrimaryKey varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@CurrentPage int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL ,
@docount bit = 0
)
AS
/**/ /*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int


/**/ /*设定排序语句.*/
IF CHARINDEX ( ' DESC ' , @Sort ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @Sort ) = 0
SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
SET @operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

SELECT @type = t.name, @prec = c.prec
FROM sysobjectso
JOIN syscolumnsc on o.id = c.id
JOIN systypest on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '

DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strFilter varchar ( 200 )
DECLARE @strSimpleFilter varchar ( 200 )
DECLARE @strGroup varchar ( 200 )

/**/ /*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1

/**/ /*设置分页参数.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))

/**/ /*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END

IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUPBY ' + @Group + ' '
ELSE
SET @strGroup = ''



declare @cTemp NVarChar ( 1000 )
declare @PageCount int , @lineCount decimal

CREATE TABLE # temp (linecount INT )

set @cTemp = ' insertinto#temp(linecount)selectcount(*)FROM ' + @Tables + @strFilter + ' ' + @strGroup

exec ( @cTemp )

select @lineCount = linecount from # temp

drop table # temp


if ( @docount = 1 )
begin
select @lineCount ' 总行数 '
end
else

begin

-- 得到总页数
set @PageCount = CEILING ( @lineCount / @strPageSize )
if @CurrentPage > @PageCount
begin
set @cTemp = ' SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE1>2 '
end
else
begin

/**/ /*执行查询语句*/

set @cTemp = ' DECLARE@SortColumn ' + @type + '
SETROWCOUNT ' + @strStartRow + '
SELECT@SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + '
SETROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + ''

end

-- print@cTemp
EXEC ( @cTemp )
end

-- ---------------------------------------------------

分页查询的方法已经很多很多，在这里我也加入成为其中一员。
SQLServer中有一个SetRowcount的的设置，它的意思是使命令的处理在响应指定的行数之后停止处理命令，利用这个特点，我们可以借用它来在一个千万行级数据表中实现高性能分页查询。先来说说实现方式：
1 、我们来假定Table中有一个已经建立了索引的主键字段ID（整数型），我们将按照这个字段来取数据进行分页。
2 、页的大小我们放在 @PageSize中
3 、当前页号我们放在 @CurrentPage中
4 、如何让记录指针快速滚动到我们要取的数据开头的那一行呢，这是关键所在！有了Set RowCount ，我们就很容易实现了。
5 、如果我们成功地滚动记录指针到我们要取的数据的开头的那一行，然后我们把那一行的记录的ID字段的值记录下来，那么，利用Top和条件，我们就很容易的得到指定页的数据了。当然，有了Set RowCount ，我们难道还用Top么？
看看SetRowcount怎么来帮我们的忙吧：

Declare @ID int
Declare @MoveRecords int

-- @CurrentPage和@PageSize是传入参数
Set @MoveRecords = @CurrentPage * @PageSize + 1

-- 下面两行实现快速滚动到我们要取的数据的行，并把ID记录下来
Set Rowcount @MoveRecords
Select @ID = ID from Table1 Order by ID

Set Rowcount @PageSize
-- 最恨为了减少麻烦使用*了，但是在这里为了说明方便，暂时用一下
Select * From Table1 Where ID >= @ID Order By ID
Set Rowcount 0
大家可以试试看，在一个1千W记录的表里面，一下子方翻页到第100页（每页100条），看看有多快！

分享]千万数量级分页存储过程（带效果演示）
效果演示：http: // www.cn5135.com / _App / Opportunities / QueryResult.aspx

CREATE PROCEDURE CN5135_SP_Pagination
/**/ /*
***************************************************************
**千万数量级分页存储过程**
***************************************************************
参数说明:
1.Tables:表名称,视图
2.PrimaryKey:主关键字
3.Sort:排序语句，不带OrderBy比如：NewsIDDesc,OrderRowsAsc
4.CurrentPage:当前页码
5.PageSize:分页尺寸
6.Filter:过滤语句，不带Where
7.Group:Group语句,不带GroupBy
效果演示：http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
***************************************************************/
(
@Tables varchar ( 1000 ),
@PrimaryKey varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@CurrentPage int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL
)
AS
/**/ /*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/**/ /*设定排序语句.*/
IF CHARINDEX ( ' DESC ' , @Sort ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
SET @operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @Sort ) = 0
SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
SET @operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type = t.name, @prec = c.prec
FROM sysobjectso
JOIN syscolumnsc on o.id = c.id
JOIN systypest on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strFilter varchar ( 1000 )
DECLARE @strSimpleFilter varchar ( 1000 )
DECLARE @strGroup varchar ( 1000 )
/**/ /*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/**/ /*设置分页参数.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @CurrentPage - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/**/ /*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUPBY ' + @Group + ' '
ELSE
SET @strGroup = ''
/**/ /*执行查询语句*/
EXEC (
'
DECLARE@SortColumn ' + @type + '
SETROWCOUNT ' + @strStartRow + '
SELECT@SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + '
SETROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + '
'
)
GO

注:
1)如表名参数为多表连接时,sort列必须指定表名;
2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
3)对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)


http://www.codeproject.com/aspnet/PagingLarge.asp

邹建的存储过程
ALTER PROC 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 ' ORDERBY ' + 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 ' @PageCountintOUTPUT ' , @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 ' SELECTTOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder )
ELSE
BEGIN
SELECT @PageCurrent = @TopN1 ,
@sql = N ' SELECT@n=@n-1,@s=CASEWHEN@n< ' + @TopN
+ N ' THEN@s+N '' , '' +QUOTENAME(RTRIM(CAST( ' + @FieldKey
+ N ' asvarchar(8000))),N '''''''' )ELSEN '''' ENDFROM ' + @tbname
+ N ' ' + @Where
+ N ' ' + @FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql ,
N ' @nint,@snvarchar(4000)OUTPUT ' ,
@PageCurrent , @sql OUTPUT
SET ROWCOUNT 0
IF @sql = N ''
EXEC (N ' SELECTTOP0 '
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname )
ELSE
BEGIN
SET @sql = STUFF ( @sql , 1 , 1 ,N '' )
-- 执行查询
EXEC (N ' SELECTTOP ' + @TopN
+ N ' ' + @FieldShow
+ N ' FROM ' + @tbname
+ N ' WHERE ' + @FieldKey
+ N ' IN( ' + @sql
+ N ' ) ' + @FieldOrder )
END
END

1 if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Paging_RowCount] ' ) and OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
2 drop procedure [ dbo ] . [ Paging_RowCount ]
3 GO
4
5 SET QUOTED_IDENTIFIER ON
6 GO
7 SET ANSI_NULLS ON
8 GO
9 -- -------------------------------------------------------------
10 -- 分页存储过程(使用RowCount)--editbySiBen
11 -- summary:
12 -- 获取表或表集合的分页数据
13 -- 当多表连接时,sort列必须指定表名
14 -- -------------------------------------------------------------
15
16 CREATE PROCEDURE Paging_RowCount
17 (
18 @Tables varchar ( 1000 ),
19 @PK varchar ( 100 ),
20 @Sort varchar ( 200 ) = NULL ,
21 @PageNumber int = 1 ,
22 @PageSize int = 10 ,
23 @Fields varchar ( 1000 ) = ' * ' ,
24 @Filter varchar ( 1000 ) = NULL ,
25 @Group varchar ( 1000 ) = NULL ,
26 @RecordCount int = 0 output
27 )
28 AS
29
30 /**/ /*DefaultSorting*/
31 IF @Sort IS NULL OR @Sort = ''
32 SET @Sort = @PK
33
34 /**/ /*Findthe@PKtype*/
35 DECLARE @SortTable varchar ( 100 )
36 DECLARE @SortName varchar ( 100 )
37 DECLARE @strSortColumn varchar ( 200 )
38 DECLARE @operator char ( 2 )
39 DECLARE @type varchar ( 100 )
40 DECLARE @prec int
41
42 /**/ /*Setsortingvariables.*/
43 IF CHARINDEX ( ' DESC ' , @Sort ) > 0
44 BEGIN
45 SET @strSortColumn = REPLACE ( @Sort , ' DESC ' , '' )
46 SET @operator = ' < '
47 END
48 ELSE
49 BEGIN
50 IF CHARINDEX ( ' ASC ' , @Sort ) > 0
51 SET @strSortColumn = REPLACE ( @Sort , ' ASC ' , '' )
52 ELSE
53 SET @strSortColumn = @Sort
54
55 SET @operator = ' > '
56 END
57
58
59 IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
60 BEGIN
61 SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
62 SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
63 END
64 ELSE
65 BEGIN
66 SET @SortTable = @Tables
67 SET @SortName = @strSortColumn
68 END
69
70 SELECT @type = t.name, @prec = c.prec
71 FROM sysobjectso
72 JOIN syscolumnsc on o.id = c.id
73 JOIN systypest on c.xusertype = t.xusertype
74 WHERE o.name = @SortTable AND c.name = @SortName
75
76 IF CHARINDEX ( ' char ' , @type ) > 0
77 SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
78
79 DECLARE @strPageSize varchar ( 50 )
80 DECLARE @strStartRow varchar ( 50 )
81 DECLARE @strFilter varchar ( 1000 )
82 DECLARE @strSimpleFilter varchar ( 1000 )
83 DECLARE @strGroup varchar ( 1000 )
84
85 /**/ /*DefaultPageNumber*/
86 IF @PageNumber < 1
87 SET @PageNumber = 1
88
89 /**/ /*Setpagingvariables.*/
90 SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
91 SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
92
93 /**/ /*Setfilter&groupvariables.*/
94 IF @Filter IS NOT NULL AND @Filter != ''
95 BEGIN
96 SET @strFilter = ' WHERE ' + @Filter + ' '
97 SET @strSimpleFilter = ' AND ' + @Filter + ' '
98 END
99 ELSE
100 BEGIN
101 SET @strSimpleFilter = ''
102 SET @strFilter = ''
103 END
104 IF @Group IS NOT NULL AND @Group != ''
105 SET @strGroup = ' GROUPBY ' + @Group + ' '
106 ELSE
107 SET @strGroup = ''
108
109 /**/ /*Getrowscount.*/
110 DECLARE @str_Count_SQL nvarchar ( 500 )
111 SET @str_Count_SQL = ' SELECT@TotalCount=count(*)FROM ' + @Tables + @strFilter
112 EXEC sp_executesql @str_Count_SQL ,N ' @TotalCountint=0output ' , @RecordCount output
113
114 /**/ /*Executedynamicquery*/
115 IF @Sort = @PK
116 BEGIN
117 EXEC (
118 '
119 DECLARE@SortColumn ' + @type + '
120 SETROWCOUNT ' + @strStartRow + '
121 SELECT@SortColumn= ' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + '
122 SETROWCOUNT ' + @strPageSize + '
123 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' =@SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + '
124 '
125 )
126 END
127 ELSE
128 BEGIN
129 EXEC (
130 '
131 DECLARE@SortColumn ' + @type + '
132 DECLARE@SortNullValue ' + @type + '
133 DECLARE@PKStartValueint
134 SET@SortNullValue=CAST( '''' as ' + @type + ' )
135 SETROWCOUNT ' + @strStartRow + '
136 SELECT@SortColumn=isNull( ' + @strSortColumn + ' ,@SortNullValue),@PKStartValue= ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + ' , ' + @PK + ' Desc
137 SETROWCOUNT ' + @strPageSize + '
138 SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE(isNull( ' + @strSortColumn + ' ,@SortNullValue) ' + @operator + ' @SortColumnor(isNull( ' + @strSortColumn + ' ,@SortNullValue)=@SortColumnand ' + @PK + ' <=@PKStartValue)) ' + @strSimpleFilter + ' ' + @strGroup + ' ORDERBY ' + @Sort + ' , ' + @PK + ' Desc
139 '
140 )
141 END
142 GO
143 SET QUOTED_IDENTIFIER OFF
144 GO
145 SET ANSI_NULLS ON
146 GO
147
148