最近换了家新公司，但是新公司没有使用分页的存储过程。那我就自个写一个往项目上套 （效率怎么样就不怎么清楚没有详细的测试过）
CREATE PROCEDURE [dbo].[pro_common_pageList]
(
@tab NVARCHAR(MAX) ,---表名
@PrimaryKey VARCHAR(100) , --主键ID
@strFld NVARCHAR(MAX) , --字段字符串
@strWhere NVARCHAR(MAX) = ' ' , --where条件
@IsDistinct BIT ,
@PageIndex INT , --页码
@PageSize INT , --每页容纳的记录数
@Order VARCHAR(255) = ' ' , --排序字段及规则,不用加order by
@Sort VARCHAR(20) = 'desc' ,
@TotalCount INT OUTPUT --总记录数
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @strSql VARCHAR(MAX);
IF @strFld IS NULL
OR @strFld = ' '
BEGIN
SET @strFld = '*';
END;
IF @Order IS NULL
OR @Order = ' '
BEGIN
SET @Order = @PrimaryKey;
END;
--计算总记录数
DECLARE @sqlRecordCount NVARCHAR(MAX); --得到总记录条数的语句
DECLARE @recordCount INT; --保存总记录条数的变量
DECLARE @distinct NVARCHAR(20)= ' ';
IF @IsDistinct = 1
BEGIN
SET @distinct = ' DISTINCT ';
END;
SET @sqlRecordCount = N'SELECT @recordCount=COUNT(1) FROM (SELECT '
+ @distinct + ' ' + @strFld + ' FROM ' + @tab + ' WHERE 1=1 '
+ @strWhere + ' ) AS TEMPTABLE';
EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
@recordCount OUTPUT;
SET @TotalCount = @recordCount; --数据查询
--当@PageIndex=1，使用TOP效率较快
IF @PageIndex = 1
BEGIN
SET @strSql = 'SELECT ' + @distinct + ' TOP ' + STR(@PageSize)
+ ' ' + @strFld + ',0 AS rownum FROM ' + @tab
+ ' WHERE 1=1 ' + @strWhere + ' ORDER BY ' + @Order + ' '
+ @Sort;
END;
ELSE
BEGIN
SET @strSql = 'SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @PrimaryKey
+ ') AS rownum FROM ';
--是否去重：1去重
IF @IsDistinct = 1
BEGIN
SET @strSql = @strSql + ' (SELECT DISTINCT ' + @strFld
+ ' FROM ' + @tab + ' WHERE 1=1 ' + @strWhere
+ ' ) AS S ';
END;
ELSE
BEGIN
SET @strSql = @strSql + ' ' + @tab + ' WHERE 1=1 '
+ @strWhere;
END;
SET @strSql = @strSql
+ ') AS Dwhere WHERE Dwhere.rownum BETWEEN '
+ CAST(( ( @PageIndex - 1 ) * @PageSize ) + 1 AS NVARCHAR(20));
SET @strSql = @strSql + ' AND '
+ CAST(( @PageIndex * @PageSize ) AS NVARCHAR(20));
SET @strSql = @strSql + ' ORDER BY ' + @Order + ' ' + @Sort;
END;
PRINT(@strSql)
EXEC (@strSql);
SET NOCOUNT OFF;
END;