Sqlserver分页查询

这个主要用于数据库查询列表数据，进行分页操作

表结构如下

得到该表的分页列表

ALTER PROCEDURE [dbo].[cp_Farm_CloseAccount_GetPageRecord]
@Page INT,
@PageSize INT,
@FarmID INT,
@OrderColumn VARCHAR(258),
@RowCount INT output
AS
DECLARE @iBeginID int
DECLARE @iEndID int

SET NOCOUNT ON
SET @iBeginID = (@Page-1) * @PageSize + 1
SET @iEndID = @iBeginID + @PageSize - 1
SET @RowCount = 0

DECLARE @SQL_CONTENT NVARCHAR(MAX) = N'';
DECLARE @SQL_WHERE NVARCHAR(MAX) = N'WHERE 1 = 1 AND IsDelete = 0';

IF(@FarmID>0)
BEGIN
SET @SQL_WHERE += N'
AND t1.FarmID = @FarmID
';
END
END
IF(LEN(@OrderColumn)<=0)
BEGIN
SET @OrderColumn='ID DESC';
END
SET @SQL_CONTENT += N'
SELECT
@RowCount = COUNT(ID)
FROM(
SELECT * FROM Farm_CloseAccount AS t1 WITH(NOLOCK)
@WHERE
)C

IF(@RowCount > 0)
BEGIN
;WITH t AS (
SELECT ROW_NUMBER() OVER ( ORDER BY '+@OrderColumn+') AS R_Number, C.*
FROM (
SELECT
t1.*,
t2.TrueName AS AddUserName --添加人
FROM Farm_CloseAccount AS t1 WITH(NOLOCK)
LEFT JOIN View_PigMGT_Admin_Admin_User AS t2 WITH(NOLOCK) ON t1.AddUserID = t2.AdminUserID
@WHERE

)C)
SELECT * FROM t
WHERE R_Number BETWEEN @iBeginID AND @iEndID;
END'

SET @SQL_CONTENT = REPLACE(@SQL_CONTENT, '@WHERE', @SQL_WHERE)
EXECUTE sp_executesql @SQL_CONTENT,
N'@iBeginID INT,@iEndID INT,@RowCount int OUTPUT,@FarmID INT,@OrderColumn VARCHAR(512) ',
@iBeginID = @iBeginID,@iEndID = @iEndID,@RowCount = @RowCount OUTPUT,
@FarmID= @FarmID,@OrderColumn = @OrderColumn;