SQL Paging Procedures

 1. Create

create proc [dbo].[SP_UpgradenHistory_referId]  

@PageIndex   INT = 1,

@PageSize    INT = 100,

@refrebyId nvarchar(500)='',

@type nvarchar(max)='All',

@RecordCount INT=0 output

as

begin

   

        select

        row_number() over(order by t.edate desc) as RowNumber,

        t.*,cast(t.edate as date)as activationDate,p.plan_amount

        ,ju.f_name uid_name,isnull(jr.f_name,t.referby) ref_name

        into #result1

        from topupdetails t with(nolock)

        inner join plans p with(nolock) on p.id=t.package

        join joinnow ju with(nolock) on t.uid=ju.uid

        left join joinnow jr with(nolock) on convert(nvarchar(50),jr.uid)=t.referby

        where (t.uid=@refrebyId or t.referby=@refrebyId) and t.remark not in('Activate')

        order by t.edate desc

        select @RecordCount=count(1) from #result1

        SELECT * FROM   #result1 WHERE  rownumber BETWEEN( @PageIndex - 1 ) * @PageSize + 1 AND( (( @PageIndex - 1 ) * @PageSize + 1 ) +  @PageSize ) - 1 ORDER  BY rownumber ASC

       

        drop table #result1

   

end

2. Execute

declare @RecordCount INT=0

exec [SP_UpgradenHistory_referId] @RecordCount=@RecordCount output

print @RecordCount

3. Execute in C#

SqlParameter[] _param = new SqlParameter[]

            {  new SqlParameter("@SearchTerm",search),

                new SqlParameter("@search",""),

                new SqlParameter("@mobile",""),

               new SqlParameter("@topup",objmodel.user_type),

               new SqlParameter("@fromdate",objmodel.fromdate),

               new SqlParameter("@todate",objmodel.todate),

               new SqlParameter("@PageIndex",objmodel.pageIndex),

               new SqlParameter("@PageSize",objmodel.pageSize),

               new SqlParameter("@RecordCount", SqlDbType.Int, 4) { Direction=ParameterDirection.Output}

            };

            _dt = _sf.returnDtBy_proc("GetCustomers_Pager", _param);

            //Int64 h =Convert.ToInt64( _param[6].Value.ToString());

            m1.totalCount = _param[8].Value.ToString();

            if (_dt.Rows.Count > 0)

            {

            }