Wrapping any SQL Server 2008 statement with skip/t

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.

Answer 1

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.

Answer 2

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?