What is the best way to paginate where I can pass my existing sql statement as a variable

so existing mySQLStatement could be passed in as a variable to a c# function?

mySQLStatement="Select field1,field2 from myTable";

myNewSQLwithPagination=convertsqltopaginationsql(mySQLStatement,PageStartNumber,PageStopNumber)

Basically I am asking how to generalize pagination by passing simple queries?

I use mssql
goodkAsked:
Who is Participating?
 
Pravin AsarPrincipal Systems EngineerCommented:
According the link

https://technet.microsoft.com/en-us/library/gg699618.aspx

SQL 2012 support musql kind of syntax

Example:

SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
0
 
goodkAuthor Commented:
Ok, in other words

I want to build a generalized function where I pass sql statement and and page start and page size and it returns the sql statement which does pagination.
0
 
Dave BaldwinFixer of ProblemsCommented:
This page http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx shows the two different methods available.  The second method only works on SQL Server 2012.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
goodkAuthor Commented:
I liked the link but I could not quite gather how I would use to crate a generalized function?
0
 
Dave BaldwinFixer of ProblemsCommented:
I don't have that part of the answer.  Those are the known methods that are available.
0
 
goodkAuthor Commented:
Please see my original question.  If any one has an answer.  I am sure many people had written a function of the sort otherwise one has to write each and every query for pagination.
0
 
ste5anSenior DeveloperCommented:
I don't think that generalization makes sense, because you can even use EXEC sproc; as data source. Here none of the above techniques will work.

Either you create a cache layer in C# which provides pagination or you specify it for each statement in T-SQL.

For simple SELECT columnlist FROM table ORDER BY columnlist; you can but the T-SQL pagination code around it.
0
 
goodkAuthor Commented:
How MS access does paging as we only submit a simple sql?
0
 
Eugene ZCommented:
could  you please clarify your question including your sql server versions\edition\sp?

What is you fron-end?"MS access? if 'yes' what version?


Comparing performance for different SQL Server paging methods
http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/

--

in case you are using sql server 2012
you can try to build your 'generic" proc  using  OFFSET FETCH Clause:

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx
0
 
Pravin AsarPrincipal Systems EngineerCommented:
With MYSQL , you can use limit and startpoint

e.g.
SELECT * FROM tblOrders  ORDER BY datetime ASC LIMIT 1 , 20";


With MSSQL, you can use ROW_NUMBER()

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 21
ORDER BY RowNum
0
 
Pravin AsarPrincipal Systems EngineerCommented:
Important aspect in paging is have sort criteria.
0
 
goodkAuthor Commented:
I am not sure if I got the answer I was looking for.  I am using MSSQL shared database on godaddy.com

my front end is asp.net  using VWD from MS
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.