SQL Server 分页存储过程

最新推荐文章于 2021-03-22 16:24:07 发布

yang_5

最新推荐文章于 2021-03-22 16:24:07 发布

阅读量948

点赞数 1

分类专栏： Sql+C# 文章标签：分页存储过程 SQL分页

本文链接：https://blog.csdn.net/yang_5/article/details/45243367

版权

Sql+C# 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

USE [ReportServerTempDB]
GO
CREATE PROCEDURE [dbo].[SeachTablePage]
(
	@TableName VARCHAR(200),	-- 表名
	@Fileds VARCHAR(500),		-- 查询的字段
	@OrderFiled VARCHAR(100),	-- 排序字段
	@IsDesc BIT ,				-- 是否降序排序
	@WhereString VARCHAR(2000), -- 查询字段
	@PageIndex INT ,			-- 当前页数
	@PageSize INT ,				-- 每页条数
	@TotalRecord INT OUTPUT		-- 返回总条数
)
AS
BEGIN
	DECLARE @OrderString VARCHAR(500)
	
	IF(@PageIndex IS NULL OR @PageIndex <= 0)
	BEGIN
		SET @PageIndex = 1
	END
	
	IF(@PageSize IS NULL OR @PageSize <= 0)
	BEGIN
		SET @PageSize = 10
	END
	
	DECLARE @StartRowID INT
	DECLARE @EndRowID INT
	SET @StartRowID = (@PageIndex - 1) * @PageSize + 1
	SET @EndRowID = @PageIndex * @PageSize
	
	IF (@WhereString is null OR @WhereString = '')
	BEGIN
	 	SET @WhereString = '1 = 1'
	END
	
	IF (@OrderFiled IS NULL OR @OrderFiled = '')
	BEGIN
		SET @OrderFiled = 'CreateDate'
	END
	
	IF (@IsDesc IS NULL OR @IsDesc = 1)
	BEGIN
		SET @OrderString = @OrderFiled + ' DESC'
	END
	ELSE
	BEGIN
		SET @OrderString = @OrderFiled + ' ASC'
	END
	
	DECLARE @TotalSQL NVARCHAR(2000)
	SET @TotalSQL = 'SELECT @Total = COUNT(*) FROM ' + @TableName + ' WHERE ' + @WhereString + ''
	EXEC sp_executesql @TotalSQL , N'@Total BIGINT OUT' , @TotalRecord OUTPUT -- 返回总记录数

	DECLARE @SelectSQL NVARCHAR(3000)
	IF(@TotalRecord <= @PageSize AND @PageIndex = 1)
	BEGIN
		SET @SelectSQL = 'SELECT ' + @Fileds + ' FROM ' + @TableName + ' WHERE ' + @WhereString + ' ORDER BY ' + @OrderString
	END
	ELSE
	BEGIN
		SET @SelectSQL = 'SELECT row_number() OVER (ORDER BY ' + @OrderString + ') AS RowId,' + @Fileds + ' FROM ' + @TableName + ' WHERE ' + @WhereString
		SET @SelectSQL = 'SELECT * FROM (' + @SelectSQL + ') AS tab WHERE RowId BETWEEN ' + ltrim(STR(@StartRowID)) + ' AND ' + ltrim(STR(@EndRowID)) + ''
	END
	print @SelectSQL
	EXEC (@SelectSQL)
END