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 @iEnd

表结构如下

得到该表的分页列表

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 ‘[email protected]+‘) 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;

总结

以上是内存溢出为你收集整理的sqlserver分页全部内容，希望文章能够帮你解决sqlserver分页所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。