Last post Oct 19, 2011 05:03 PM by Lannie
Sep 02, 2004 09:30 AM|pravs|LINK
CREATE PROCEDURE [Get_Customers_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records
CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, CompanyName nvarchar(40), ContactName nvarchar (30), ContactTitle nvarchar (30), Phone nvarchar (24), Fax nvarchar (24) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( CompanyName,
ContactName, ContactTitle, Phone, Fax ) SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage
- 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total
number of records available as an output parameter SELECT @TotalRecords = COUNT(*) FROM Customers
Oct 19, 2011 04:25 PM|petterb|LINK
Oracle is designed diferently from SQL server (Sybase). And it is not convenient to write SP to retrieve a few records. The PL/SQL will accept as an error if your select statement retrive more than one row. to work with multiple rows you should use cursour
or in the latest versions of oracle you can use the new statement MERGE. ther is not any #tables.
more you can find in: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Oct 19, 2011 05:03 PM|Lannie|LINK
It is better to create VIEWs than TEMP tables as well.
But many grid controls today have porgrammable, custom paging built in so that you do not have to go back to database PL/SQL.
(DevExpress, Telerik, and other grid controls)