SQL SERVER 2000 高效分页查询语句

最新推荐文章于 2024-08-29 08:57:31 发布

yejiansnake

最新推荐文章于 2024-08-29 08:57:31 发布

阅读量6.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： .NET 数据库文章标签： sql server string sql null insert 存储

本文链接：https://blog.youkuaiyun.com/yejiansnake/article/details/1902660

.NET 同时被 2 个专栏收录

4 篇文章

订阅专栏

数据库

1 篇文章

订阅专栏

本文介绍了一种将已有的SQL查询语句封装为分页存储过程的方法。该方法通过输入原始SQL语句、唯一标识符及其类型、起始行索引及每页的最大行数等参数，自动生成分页SQL存储过程。适用于需要实现分页功能的场景。

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

        /// <summary>
        /// 将已经存在的SQL语句包装成分页SQL存储过程。
        /// </summary>
        /// <param name="sourceSql">已经存在的SQL语句。</param>
        /// <param name="key">对于已经存在的SQL语句中能唯一标识的字段名称。</param>
        /// <param name="keyType">对于key在SQL中的类型名称。</param>
        /// <param name="startRowIndex">分页起始记录索引号。</param>
        /// <param name="maximumRows">每页最大记录数。</param>
        /// <returns>分页SQL存储过程语句。</returns>
        public static string WrapPagingProcedure(string sourceSql, string key, string keyType, int? startRowIndex, int? maximumRows)
        {
            if (startRowIndex == null || maximumRows == null)
            {
                return sourceSql;
            }
            startRowIndex++;

            string sql =
                "DECLARE @PK " + keyType + " " +
                "DECLARE @tblPK TABLE(PK " + keyType + " NOT NULL PRIMARY KEY) " +
                "DECLARE @PagingSize int " +
                "DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR " +
                "SELECT " + key + " FROM(" + sourceSql + ") AS T " +
                "OPEN PagingCursor " +
                "FETCH RELATIVE " + startRowIndex.ToString() + " FROM PagingCursor INTO @PK " +
                "SET @PagingSize = " + maximumRows.ToString() + " " +
                "WHILE @PagingSize > 0 AND @@FETCH_STATUS = 0 " +
                "BEGIN " +
                "   INSERT @tblPK(PK) VALUES(@PK) " +
                "   FETCH NEXT FROM PagingCursor INTO @PK " +
                "   SET @PagingSize = @PagingSize - 1 " +
                "END " +
                "CLOSE PagingCursor " +
                "DEALLOCATE PagingCursor " +
                "SELECT * FROM(" + sourceSql + ") AS T INNER JOIN @tblPK temp ON T." + key + " = temp.PK ";

return sql;
}