ALTER PROCEDURE [dbo].[prPager](
@PageIndex INT = 0, --起始位置
@PageSize INT = 10, --页大小，当小于等于0时显示所有记录
@TableName NVARCHAR(100),
@WhereClause NVARCHAR(1000) = ' ', --条件语句
@OrderByClause NVARCHAR(200), --排序语句
@RowCount INT = 0 OUTPUT --总记录数
)
AS
BEGIN
-- Initialize Parameters
IF (@PageIndex IS NULL)
BEGIN
SET @PageIndex=0
END
IF (@PageSize IS NULL)
BEGIN
SET @PageSize=10
END
IF(@WhereClause IS NULL OR @WhereClause='')
BEGIN
SET @WhereClause = ' 1=1 '
END
IF(@OrderByClause IS NULL OR @OrderByClause = '')
BEGIN
SET @OrderByClause = ' [Id] DESC ';
END
-- Initialize Parameters
DECLARE @Sql NVARCHAR(4000)
IF(ISNULL(@RowCount,0) <= 0)
BEGIN
SET @Sql = 'SELECT @RowCount = COUNT(1) FROM '+@TableName+' WHERE ' + @WhereClause;
EXEC sp_executesql @stmt = @sql, @params = N'@RowCount AS INT OUTPUT',@RowCount = @RowCount OUTPUT
END
IF(@PageSize > 0)
BEGIN
SET @Sql =(
'SELECT *'
+ ' FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @OrderByClause + ' ) AS ROWNUM'+
+ ' FROM '+@TableName+' '
+ ' WHERE ' + @WhereClause + ') AS A'
+ ' WHERE ROWNUM > '+STR(@PageSize*@PageIndex)+' AND ROWNUM <= '+STR(@PageSize*@PageIndex+@PageSize))
+ ' ORDER BY '+@OrderByClause
EXEC sp_executesql @Sql
END
ELSE
BEGIN
SET @Sql = 'SELECT * FROM '+@TableName+' WHERE ' + @WhereClause + ' ORDER BY '+ @OrderByClause;
EXEC sp_executesql @Sql
END
END
