项目中有很多小型的表(数据量不大),都需要实现分页查询,因此实现了一个通用的分页。
建立好如下的存储过程,以后要分页,直接调用改存储过程就可以了。
注意:数据量大、性能要求高的,请个性化处理。
- ALTER PROCEDURE [dbo].[COMMON_PROCEDURE_SelectWithPage]
- @Sql VARCHAR(5000),
- @CurrentPageNo INT,
- @PageSize INT,
- @TotalNum INT OUTPUT
- AS
- SET NOCOUNT ON
- DECLARE @SqlCmd VARCHAR(5000)
- ------------------------------------------ --查询数据
- SET @SqlCmd = 'SELECT * FROM (' + @Sql + ') A WHERE RowIndex BETWEEN ' + CONVERT(VARCHAR,(@CurrentPageNo-1) * @PageSize + 1) + ' AND ' + CONVERT(VARCHAR,@CurrentPageNo * @PageSize)
- EXEC(@SqlCmd) PRINT (@SqlCmd)
- ------------------------------------------ --求记录总数
- IF @TotalNum = -1
- BEGIN
- CREATE TABLE #Temp1(num INT)
- INSERT INTO #Temp1
- EXEC('SELECT count(*) FROM (' + @Sql + ') A')
- SELECT @TotalNum=(SELECT * FROM #Temp1)
- DROP TABLE #Temp1
- END
用法很简单,但必须在传入的SQL中使用ROW_NUMBER() OVER() AS RowIndex :
- DECLARE @Sql VARCHAR(5000)
- DECLARE @CurrentPageNo INT
- DECLARE @PageSize INT
- DECLARE @TotalNum INT
- SET @CurrentPageNo = 100
- SET @PageSize = 10
- SET @TotalNum = -1
- SET @Sql = ' SELECT *, ROW_NUMBER() OVER (ORDER BY 排序字段) AS RowIndex FROM 表名 A WITH (NOLOCK) '
- EXEC [dbo].[COMMON_PROCEDURE_SelectWithPage] @Sql,@CurrentPageNo,@PageSize,@TotalNum OUTPUT
- SELECT @TotalNum