Wrapping any SQL Server 2008 statement with skip/take operators

Question

Given any SELECT statement, I would like to wrap it with skip and take operators.

For instance, for Oracle I created this function:

public override string WrapSelectSqlWithPagination(string sql, int skipRows, int numberOfRows) 
{
    string innerSql = String.Format("select /* FIRST_ROWS(n) */ a.*, ROWNUM rnum from ({0}) a where ROWNUM <= {1}", sql, skipRows + numberOfRows);
    return String.Format("select * from ({0}) where rnum > {1}", innerSql, skipRows);
}

It works perfectly.

I would like to do the same thing for SQL Server, is it possible?

Mind that I don't know anything about sorting in advance.

Thanks.

I found this good link from SO which I hope overlaps with what you need?! http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging — SGB, Nov 30 '11 at 13:48
You realize the `TOP` clause isn't relevant without an `ORDER BY`? There is no guaranteed order in SQL. — JNK, Nov 30 '11 at 13:57
Yes, I know that. But who needs to care about it is the one calling the function, not me :) — andrecarlucci, Nov 30 '11 at 14:59

score 2 · Answer 1 · answered Nov 30 '11 at 14:10

2

You can use this sql template to get the desired range of records for SQL.

WITH [AnyVariable] AS
(
select * ,ROW_NUMBER() over(order by [Col1]) AS 'RowNum'
     from [Table1]
)
SELECT * FROM [AnyVariable]
WHERE RowNum BETWEEN 3 AND 6

Just replace the things in [] with your stuff. Remember to remove the []. And then use this in your method above.

answered Nov 30 '11 at 14:10

Azhar Khorasany

2,712
16
20

This solution doesn't work since it requires to know the column used for sorting in advance :( – andrecarlucci Nov 30 '11 at 15:03
@andrecarlucci - I provided this solution before you updated your question. See the time of question update and the time I answered. I can update the answer soon. – Azhar Khorasany Jul 16 '12 at 16:17

score -1 · Answer 2 · answered Nov 30 '11 at 17:18

Ok, I got it. It's probably very slow but it works:

public override string WrapSelectSqlWithPagination(string sql, int skipRows, int numberOfRows) {
        Regex regex = new Regex("SELECT", RegexOptions.IgnoreCase);
        sql = regex.Replace(sql, "SELECT TOP 2147483647 ", 1);

        string innerSql =
            @"select * into #TempTable from (
                        select * ,ROW_NUMBER() over(order by aaa) AS rownum from (
                            select 'aaa' as aaa, * from  (
                                {0}
                            )as t1
                        )as t2
                    ) as t3
                where rownum between {1} and {2}
                alter table #TempTable drop column aaa
                alter table #TempTable drop column rownum
                select * from #TempTable
                drop table #TempTable
            ";
        return String.Format(innerSql, sql, skipRows+1, skipRows + numberOfRows);
    }

Is there a better way to do this?

Wrapping any SQL Server 2008 statement with skip/take operators

2 Answers2

Linked