46505

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?

<strong>Mind that I don't know anything about sorting in advance.</strong>

Thanks.

Answer1:

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.

Answer2:

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?

Recommend

  • Oracle query results pagination without TABLE FULL SCAN data access method
  • Expecting exact results when using contains clause in Oracle
  • Date of max id: sql/oracle optimization
  • Row_number() function for Informix
  • mysql table locked after php crashes
  • Weird LEFT OUTER JOIN on Includes eager loading of rails 3
  • ASP.NET, C# How to Pass a StringQuery to a custom SQL Command
  • Connecting Google Cloud SQL with Wordpress on Google Compute Engine
  • Passing parameter through “window.location.href”
  • How can I implement a custom jqGrid delete button?
  • SQL Server re-calculate or not?
  • Possible to get the line number of the currently executing sproc in SQL Server?
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • It is possible use the same sql azure instance from two different cloud service of two different sub
  • TSQL Rolling Average of Time Groupings
  • distinct values from multiple fields within one table ORACLE SQL
  • Oracle - Second level subquery cannot see field from main query
  • What is corresponding c++ data type to SQL numeric(18,0) data type?
  • How can Delete be both a DDL and a DML statement
  • How to 'create temp table as select' in Slick?
  • pyodbc doesn't report sql server error
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Resize panoramic image to fixed size
  • Volusion's generic SQL folder, functionality
  • How to get next/previous record number?
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • How do you join a server to an Active Directory (domain)?
  • coudnt use logback because of log4j
  • How does Linux kernel interrupt the application?
  • apache spark aggregate function using min value
  • Is it possible to post an object from jquery to bottle.py?
  • Sorting a 2D array using the second column C++
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • Python/Django TangoWithDjango Models and Databases
  • java string with new operator and a literal