Last post Jan 24, 2013 10:27 PM by CsharpAsp.net
Jan 22, 2013 03:44 AM|CsharpAsp.net|LINK
Could you please help me with creating stored procedure in oracle that could be utilized in custom pagination/sorting.
I have read it utlized row num property but I have not used this before.
So my strored procedure would have the following inputs:
numberofrows, pageindex, sortcolumnname, sortorderby
and output would be totalrecords and result set.
Jan 22, 2013 03:52 AM|Pbalan.in|LINK
I have create the stored procedure for custom paging using Sql 2008. The table contains 1000000 lakhs countries and i selected the certain countries based on the page index.
ALTER PROCEDURE [dbo].[sp_GetCountries]
-- Add the parameters for the stored procedure here
@StartIndex bigint,@PageSize bigint
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @RecCount numeric(18,0)=0,@Query nvarchar(max);
create Table #Temp (IndexNo numeric(18,0),ID numeric(18,0),Name nvarchar(50));
create index index_Temp on #Temp(ID, Name)
-- Insert statements for procedure here
Insert into #Temp Select ROW_NUMBER() OVER (ORDER BY Id) Rownum,ID,Name from Country_Master;
set @RecCount=(Select COUNT(*) from #Temp);
Set @Query = ('Select top ' + convert(nvarchar,@PageSize,50) + ' ' + convert(nvarchar,@RecCount,50) + ' as TotalCount,ID,Name from #Temp where IndexNo > ' + convert(nvarchar,@StartIndex,50) + ';')
--Exec [dbo].[sp_GetCountries] 0,10;
Jan 22, 2013 04:04 AM|CsharpAsp.net|LINK
i would like to avoid temp table and dynamic query.
I had seen an implementation without the above two but not able to recall that,
Jan 22, 2013 10:19 PM|CsharpAsp.net|LINK
Jan 24, 2013 10:27 PM|CsharpAsp.net|LINK
no experts here