使用“开始"和“长度"参数从SQL 2000中分页的好方法是什么? [英] What is a good way to paginate out of SQL 2000 using Start and Length parameters?

问题描述

我被赋予了重构现有存储过程的任务，以便对结果进行分页. SQL Server是SQL 2000，因此我无法使用ROW_NUMBER分页方法.存储的过程已经相当复杂，在执行sp_executesql之前先将一个大型sql语句的块构建在一起，并且具有各种可用的排序选项.

从Google出来的第一个结果似乎是一个好方法，但我认为该示例是错误的，因为第二种排序需要颠倒，并且起始小于页面长度的情况会崩溃.该页面上的第二个示例似乎也不错，但是SP使用的是pageNumber而不是起始记录.整个临时表看起来似乎会浪费性能.

我正在沿着这条道路前进，但是它似乎很慢而且令人困惑，我必须对Sort顺序进行很多REPLACE方法才能使它正确显示.

我还有其他更简单的方法吗?

解决方案

玩了一段时间之后，似乎只有一种方法可以做到这一点(使用Start和Length参数)，这就是临时表. /p>

我最终的解决方案是不使用@start参数，而是使用@page参数，然后使用

    SET @sql = @sql + N'

    SELECT * FROM
    (
    SELECT TOP ' + Cast( @length as varchar) + N' * FROM 
            (
            SELECT TOP ' + Cast( @page*@length as varchar) + N'
                 field1,
                 field2 
                 From Table1
                 order by field1 ASC
            )  as Result   
            Order by Field1 DESC
     )  as Result
     Order by Field 1 ASC'

原始查询比这里显示的要复杂得多，并且order by在至少3个字段上进行了排序，并由一个长CASE子句确定，要求我使用一系列REPLACE函数来获取正确的字段订单.

I have been given the task of refactoring an existing stored procedure so that the results are paginated. The SQL server is SQL 2000 so I can't use the ROW_NUMBER method of pagination. The Stored proc is already fairly complex, building chunks of a large sql statement together before doing an sp_executesql and has various sorting options available.

The first result out of google seems like a good method but I think the example is wrong in that the 2nd sort needs to be reversed and the case when the start is less than the pagelength breaks down. The 2nd example on that page also seems like a good method but the SP is taking a pageNumber rather than the start record. And the whole temp table thing seems like it would be a performance drain.

I am making progress going down this path but it seems slow and confusing and I am having to do quite a bit of REPLACE methods on the Sort order to get it to come out right.

Are there any other easier techniques I am missing?

解决方案

After playing with this for a while there seems to be only one way of really doing this (using Start and Length parameters) and that's with the temp table.

My final solution was to not use the @start parameter and instead use a @page parameter and then use the

    SET @sql = @sql + N'

    SELECT * FROM
    (
    SELECT TOP ' + Cast( @length as varchar) + N' * FROM 
            (
            SELECT TOP ' + Cast( @page*@length as varchar) + N'
                 field1,
                 field2 
                 From Table1
                 order by field1 ASC
            )  as Result   
            Order by Field1 DESC
     )  as Result
     Order by Field 1 ASC'

The original query was much more complex than what is shown here and the order by was ordered on at least 3 fields and determined by a long CASE clause, requiring me to use a series of REPLACE functions to get the fields in the right order.

这篇关于使用“开始"和“长度"参数从SQL 2000中分页的好方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！