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.
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.
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?
