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;

 

转载于:https://www.cnblogs.com/wangshitou/p/10214149.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值