数据库分页存储过程
CREATE PROCEDURE [dbo].[P_DOCGetTest]--20,2,0
@orderCellName NVARCHAR(50),
@sortOrder NVARCHAR(50),
@pageSize INT,
@pageIndex INT,
@RowCount INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
DECLARE @SQL_COUNT NVARCHAR(MAX)
DECLARE @BEGIN INT
DECLARE @END INT
SET @BEGIN = CAST(((@PageIndex - 1) * @PageSize + 1) AS VARCHAR(15))
SET @END = CAST((@PageSize * @PageIndex) AS VARCHAR(15))
IF(ISNULL(@orderCellName,'')='')
BEGIN
SET @orderCellName='T.Id'
END
IF(ISNULL(@sortOrder,'')='')
BEGIN
SET @sortOrder='ASC'
END
SET @SQL='SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderCellName + ' ' + @sortOrder + ') as RowId
T.Id,
T.Name,
T.Remark
FROM [test] AS T '
SET @SQL_COUNT = 'SELECT @RowCount = COUNT(0) FROM (' + @SQL + ') AS t'
SET @SQL='SELECT * FROM (' + @SQL + ') AS T WHERE T.RowId ' +
'BETWEEN ' + CONVERT(VARCHAR(8),@Begin) + ' AND ' + CONVERT(VARCHAR(8),@End)
EXEC SP_EXECUTESQL @SQL_COUNT,N'@RowCount INT OUTPUT',@RowCount OUTPUT
EXEC(@SQL)
PRINT @SQL
END
