mssql 根据行号分页的store procedure

mssql 根据行号分页的store procedure

if exists(select * from sys.procedures where name='PROC_GetListRecordByPage')
drop procedure PROC_GetListRecordByPage

go

CREATE PROCEDURE [dbo].[PROC_GetListRecordByPage]
@tblName VARCHAR(255), -- 表名
@fldName VARCHAR(255), -- 主键字段名
@PageSize INT = 10,-- 页尺寸
@PageIndex INT = 1, -- 页码
@IsReCount INT = 0, -- 0 返回记录总数; 1只返回数据; 2 都返回
@OrderType BIT = 0, -- 设置排序类型, 非 0 值则降序
@strWhere VARCHAR(3000) = '', -- 查询条件 (注意: 不要加 where)
@returnFld VARCHAR(1000) = '*' --需要返回的列
AS
BEGIN
DECLARE @tempSql VARCHAR(6000) -- 临时语句
DECLARE @strSqlAll VARCHAR(6000) -- 主语句
DECLARE @strSqlCnt VARCHAR(400) -- 数据数量
DECLARe @OrderTypeStr NVARCHAR(20)

IF @OrderType != 0 or @OrderType is null
SET @OrderTypeStr = 'DESC'
ELSE
SET @OrderTypeStr = 'ASC'

SET @tempSql = 'SELECT ROW_NUMBER() OVER (ORDER BY [' + @fldName + '] ' + @OrderTypeStr + ') AS rowId,' + @returnFld + ' FROM [' + @tblName + '] '

IF @strWhere != ''
SET @tempSql = @tempSql + ' WHERE ' + @strWhere

SET @strSqlAll = 'SELECT * FROM (' + @tempSql + ') t WHERE rowId BETWEEN ' + CAST(((@PageIndex - 1) * @PageSize + 1) AS VARCHAR)
+ ' AND ' + CAST((@PageIndex * @PageSize ) AS VARCHAR);
--PRINT @strSqlAll
EXEC (@strSqlAll)
IF @IsReCount != 1
IF @strWhere != ''
SET @strSqlCnt = 'SELECT COUNT(1) AS Total FROM [' + @tblName + '] WHERE ' + @strWhere
ELSE
SET @strSqlCnt = 'SELECT COUNT(1) AS Total FROM [' + @tblName + ']'
EXEC (@strSqlCnt)

END


---测试代码
execute PROC_GetListRecordByPage 'V_CreditCardDrawList','BaseInfoID',10,1,0,0,'','*'