|
|
Posted by Erland Sommarskog on 04/26/06 00:36
John (johnxhc@yahoo.com) writes:
> I do not know if it is possible, but here is what I want to do.
> I want to allow user to page the SQL result, so he could decides to
> return from row 10 to row 20, or row 100 to 200, without returns the
> whole resultset. Every time he sends another request, I do not mind to
> hit the database again, (I do not want to cache the result in the
> middle tier server, scalability issue), and I know that I could achieve
> this with CURSOR, but unfortunately the FOR XML is not allowed in a
> CURSOR statement .
> (I know that I could achieve what I want to do by writing custom code
> in the middle tier, but I just want to see if there is a way to do this
> on the database side.)
>
> Any comments & suggestion is greatly appreciated.
> Thanks in advance.
>
> (I am using SQL2005)
If you are on SQL 2005, I would suggest that you lool into the
row_number function. Here is a sample:
SELECT OrderID, OrderDate, CustomerID
FROM (SELECT OrderID, OrderDate, CustomerID,
rowno = row_number() OVER (ORDER BY OrderID)
FROM Northwind..Orders) AS a
WHERE rowno BETWEEN 100 AND 200
FOR XML AUTO
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|