Please suggest me a method or provide me a query to fetch data Pagewise from Oracle and SQL server.?

Question

Eg. In My sql there is a Keyword 'Limit' which can ftech data from table giving a bookmark and rowcount.

SELECT *
FROM `product_detail`
LIMIT 0 , 30
..
Exacly like this please provide me a method to return rows like this...
I need to display pagewise records for thousandsof records

Answer 1

There is no LIMIT keyword in SQL Server 2000 (am not sure about SQL Server 2005). You have to provide a custom tailored solution to page your results. The following Stored Procedure in T-SQL (SQL for MS- SQL Server) will provide you with an explanation on how to proceed.



The Procedure LOOKS complicated but it is not. You simply provide it with a PageIndex and a PageSize and it returns the desired results. For example, for a Page Size of 30 you send the SP the parameters 0 and 30 to retrieve the first page (with 30 results per page). For the next page, you send the SP the parameters 1 and 30, and so on...



Here is the code:



CREATE PROCEDURE northwind_OrdersPaged

( @PageIndex int, @PageSize int) AS

BEGIN

DECLARE @PageLowerBound int

DECLARE @PageUpperBound int

DECLARE @RowsToReturn int



-- First set the rowcount

SET @RowsToReturn = @PageSize * (@PageIndex + 1)

SET ROWCOUNT @RowsToReturn



-- Set the page bounds

SET @PageLowerBound = @PageSize * @PageIndex

SET @PageUpperBound = @PageLowerBound + @PageSize + 1



-- Create a temp table to store the select results

CREATE TABLE #PageIndex

( IndexId int IDENTITY (1, 1) NOT NULL, OrderID int)

-- Insert into the temp table

INSERT INTO #PageIndex (OrderID)

SELECT OrderID FROM Orders

ORDER BY OrderID DESC



-- Return total countSELECT COUNT(OrderID) FROM Orders



-- Return paged results

SELECT O.*

FROM Orders O, #PageIndex PageIndex

WHERE O.OrderID = PageIndex.OrderID AND

PageIndex.IndexID > @PageLowerBound AND PageIndex.IndexID < @PageUpperBound

ORDER BY

PageIndex.IndexID

END

Answer 2

that's a solid question to ask. One answer is that in case you inspect human historic past in its totality there is not any reason to grant government greater capability than mandatory and no reason to think of that this capability heavily isn't abused. finished protection demands a balancing of government authority and privateness. If government is given too lots authority, how freed from a society do you quite stay in? after all, we define techniques like freedom and liberty precisly by ability of pointing to regulations on government capability. Our founding fathers made this fee judgment whilst they drafted the Fourth exchange. At a undeniable element, too lots government capability comes at too super a fee. additionally, in any respect situations of conflict we've regarded back with the income of hindsight to observe our errors (Alien & Sedition Act, Japense internment camps... ect.). A slippery slope would desire to okay exist. the only thank you to circumvent falling down that slope is to observe your footing. that's finished by ability of having an clever communique concerning the threats we are dealing with and maximum useful (and constrained) ability by ability of which we are able to bolster our own protection.

Answer 3

i dont know what you meant by page wise but you can use this query



select * from product_detail where rowid <=30

select * from product_detail where rowid>30 and rowid <=60



and so on...