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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
goodkAuthor Commented:
I liked the link but I could not quite gather how I would use to crate a generalized function?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.