分页存储过程

调用实例(支持多表Join查询)：

EXEC up_Pagition 'datatable','id','*',pageSize,pageIndex,'','','id asc'

 

代码

-- ----------------------- 代码开始 ------------------------------

CREATE PROCEDURE [ dbo ] . [ up_Pagition ]
@tableNames VARCHAR ( 200 ), -- 表名，可以是多个表，但不能用别名
@primaryKey VARCHAR ( 100 ), -- 主键，可以为空，但 @order为空时该值不能为空
@fields VARCHAR ( 200 ), -- 要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@pageSize INT , -- 每页记录数
@currentPageIndex INT , -- 当前页，0表示第1页
@filter VARCHAR ( 200 ) = '' , -- 条件，可以为空，不用填 where
@group VARCHAR ( 200 ) = '' , -- 分组依据，可以为空，不用填 group by
@order VARCHAR ( 200 ) = '' -- 排序，可以为空，为空默认按主键升序排列，不用填 order by
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 = ' WHERE 1=1 '
ELSE
SET @filter = ' WHERE ' + @filter
IF @group <> ''
SET @group = ' GROUP BY ' + @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 sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t 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 * @currentPageIndex + 1
print @TopRows
print @operator
EXEC ( '
DECLARE @sortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @sortColumnBegin= ' + @sortColumn + ' FROM ' + @tableNames + ' ' + @filter + ' ' + @group + ' ORDER BY ' + @order + '
SET ROWCOUNT ' + @pageSize + '
SELECT ' + @fields + ' FROM ' + @tableNames + ' ' + @filter + ' AND ' + @sortColumn + '' + @operator + ' @sortColumnBegin ' + @group + ' ORDER BY ' + @order + '
' )
END

 

转载于:https://www.cnblogs.com/bndy/articles/1692153.html