Sql Server 分页存储过程

最新推荐文章于 2023-10-08 14:37:22 发布

转载最新推荐文章于 2023-10-08 14:37:22 发布 · 488 阅读

文章标签：

#database #c# #.net

MS-Sql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种使用存储过程实现的高效SQL分页查询方法，通过动态SQL构造实现了灵活的排序与过滤条件设置，适用于多种数据库场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

效率不错的分页


/****** Object:  StoredProcedure [dbo].[PROC_PAGECHANGE]    Script Date: 02/03/2015 09:39:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[PROC_PAGECHANGE]
(
 @TableName VARCHAR(50),            --表名
 @ReFieldsStr VARCHAR(2000) = '*',   --字段名(全部字段为*)
 @OrderString VARCHAR(200),         --排序字段(必须!支持多字段不用加order by)
 @OrderType	INT,					--排序类型 0 升序, 1 降序
 @WhereString varchar(1000) =N'',	--条件语句(不用加where)
 @PageSize INT,                     --每页多少条记录
 @PageIndex INT = 1 ,               --指定当前为第几页
 @TotalRecord INT OUTPUT            --返回总记录数
)
AS
 
BEGIN  
	--处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    Declare @OrderStr nvarchar(10);  
    --设置排序方式
    SET @OrderStr = ' ASC '; 
	IF @OrderType = 1
	BEGIN
		SET @OrderStr = ' DESC ';
	END
     
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString + @OrderStr + ') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    --第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
--PRINT @SqlString
END

  public DataTable  GetPage(int page, int pageIndex,ref int totalPages,ref int totalCount)
       {
            SqlCommand myCommand = new SqlCommand("PROC_PAGECHANGE", DBHelper.Connection);
            myCommand.CommandType = CommandType.StoredProcedure;
            //添加输入查询参数、赋予值
            myCommand.Parameters.Add("TableName", SqlDbType.VarChar);
            myCommand.Parameters["TableName"].Value = "T1";

            myCommand.Parameters.Add("ReFieldsStr", SqlDbType.VarChar);
            myCommand.Parameters["ReFieldsStr"].Value = "*";

            myCommand.Parameters.Add("OrderString", SqlDbType.VarChar);
            myCommand.Parameters["OrderString"].Value = "ZFTime";

            myCommand.Parameters.Add("OrderType", SqlDbType.Int);
            myCommand.Parameters["OrderType"].Value = "0";

            myCommand.Parameters.Add("WhereString", SqlDbType.VarChar);
            myCommand.Parameters["WhereString"].Value = "1=1";

            myCommand.Parameters.Add("PageSize", SqlDbType.Int);
            myCommand.Parameters["PageSize"].Value = page;

            myCommand.Parameters.Add("PageIndex", SqlDbType.Int);
            myCommand.Parameters["PageIndex"].Value = pageIndex;


            //添加输出参数
            myCommand.Parameters.Add("TotalRecord", SqlDbType.Int);
            myCommand.Parameters["TotalRecord"].Direction = ParameterDirection.Output;

            SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);

            int pages = (int)(Math.Ceiling(Convert.ToDecimal(myCommand.Parameters["TotalRecord"].Value) / 20));
           // var data = dataTable;
            totalPages = pages;
            return dataTable;
        }