分页存储过程 BY Bndy

代码

-- Author Bndy
-- QQ/MSN 81795705/bndy5337@msn.com
-- Email bndy5337@163.com
-- HomePage http://www.bndy.net

alter PROCEDURE [ dbo ] . [ up_Pagination ]
@tableName nvarchar ( 50 ),
@primaryKey nvarchar ( 20 ),
@selectFileds nvarchar ( 100 ) = N ' * ' ,
@condition nvarchar ( 1000 ) = N ' 1>0 ' ,
@sortString nvarchar ( 100 ) = N ' ID DESC ' ,
@pageIndex int = 0 ,
@pageSize int = 10
AS

BEGIN
SET NOCOUNT ON ;

IF @selectFileds = '' OR @selectFileds IS NULL
SET @selectFileds = N ' * '

IF @condition = '' OR @condition IS NULL
SET @condition = N ' 1>0 '

IF @sortString = '' OR @sortString IS NULL
SET @sortString = N ' ' + @primaryKey + ' desc '

ELSE
IF @primaryKey <> '' AND @primaryKey IS NOT NULL AND CHARINDEX ( @primaryKey + ' ' , @sortString , 0 ) < 0
SET @sortString = @sortString + N ' , ' + @primaryKey + ' desc '

DECLARE @sql nvarchar ( 4000 )
SET @sql = N ' if (SELECT OBJECT_ID( '' #tmpTable '' )) IS NOT NULL DROP TABLE #tmpTable; '

SET @sql = @sql + (N ' SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @sortString + ' ) AS ROWNUMBER INTO #tmpTable FROM ' + @tableName + ' WHERE ' + @condition + ' ORDER BY ' + @sortString + '' ) + ' ; '

IF @pageIndex = 0
SET @sql = @sql + N ' SELECT TOP ' + rtrim ( @pageSize ) + ' ' + @selectFileds + ' FROM #tmpTable ORDER BY ' + @sortString
ELSE
SET @sql = @sql + N ' SELECT TOP ' + rtrim ( @pageSize ) + ' ' + @selectFileds + ''
+ ' FROM #tmpTable '
+ ' WHERE ROWNUMBER > ( '
+ ' SELECT MAX(ROWNUMBER) FROM ( '
+ ' SELECT TOP ' + rtrim (( @pageIndex ) * @pageSize ) + ' ROWNUMBER FROM #tmpTable ORDER BY ' + @sortString
+ ' ) AS tbl '
+ ' ) '
+ ' ORDER BY ' + @sortString

exec ( @sql )

-- 返回总记录数
declare @rowCount int , @sqlRowCount nvarchar ( 4000 )
set @sqlRowCount = ' SELECT @rowCount = COUNT(*) FROM ' + @tableName + ' WHERE ' + @condition
exec sp_executesql @sqlRowCount , N ' @rowCount int output ' , @rowCount output
return @rowCount

END

 

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