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)

            {

            }

Date Formate In Sql server

Change custom date format in  sql query in ms sql server 

 format(top_update,'dd/MMM/yyyy') as top_updates

Examples 

select row_number() over(order by joinnow.top_update desc ) as row, user_id,sponser_id,f_name,mobile,email,telephone,format(top_update,'dd/MMM/yyyy') as top_updates,

(select address+' , '+(select districtname from districts where districtid=joinnow.districtid)+', '+(select statename from state_master where stateid=joinnow.stateid)+', Pincode- '+pincode) as address,

(select plan_name from plans where id=joinnow.package) as package,convert(varchar,edate,103) as edate,remark,

(select products from products where id=joinnow.product_id) as products,format(product_status_date,'dd/MM/yyyy') pp,

(case product_status when 0 then 'Pending' when 1 then 'Delivered' when 2 then 'Cancel' end) as status,product_status

from joinnow where product_id is not null and product_status=0 and top_up=1 and product_id != 0 order by top_update desc

MS Sql server

1. Execute output param in procedure in sql server 

declare @RecordCount int=1

exec [Getcustomers_pager] @topup='0',@RecordCount=@RecordCount output

print @RecordCount

2. Define out parametered in procedure in sql server

CREATE PROCEDURE [dbo].[Getcustomers_pager] @SearchTerm  VARCHAR(100) = '', 

@PageIndex   nvarchar(max) = 1, 

@PageSize    nvarchar(max) = 100,

@mobile      NVARCHAR(max)='', 

@search      INT=0,

@fromdate   nvarchar(max)='',

@todate      nvarchar(max)='',

@topup varchar(10)= '',

@RecordCount INT output 

AS 

BEGIN 

SET nocount ON; 

declare @query nvarchar(max)='',@query1 nvarchar(max)=''

set @query = 'select row_number() over(order by m.id desc) as RowNumber,

case m.status when 1 then ''Active'' else ''Block'' end as blockstatus,

case m.status when 1 then ''success'' else ''danger'' end as blockclass,

case m.status when 1 then ''Block'' else ''Active'' end as blockbtn,

case m.status when 1 then ''danger'' else ''success'' end as blockbtnclass,

format(m.edate,''dd/MM/yyyy'') as date,format(m.top_update,''dd/MM/yyyy'') as top_update1,

(select  max(remark) from cto_achievers where status=1 and uid=m.uid) rank_name,

m.*,l.password,l.trans_password

from Joinnow m with(nolock) inner join login l on m.uid=l.uid 

where l.block=0'

set @query1='select @count= count(m.id) from Joinnow m with(nolock) inner join login l on m.uid=l.uid where l.block=0'

IF(@topup != '')

BEGIN

set @query=@query+' and m.top_up='''+@topup+''''

set @query1=@query1+' and m.top_up='''+@topup+''''

END

ELSE IF(@SearchTerm !='')

BEGIN

set @query=@query+' and m.user_id='+@SearchTerm+' OR l.password ='''+@SearchTerm+''''

set @query1=@query1+' and m.user_id ='+@SearchTerm+' OR l.password='''+@SearchTerm+''''

END

 

ELSE IF(@fromdate !='' or @todate != '')

BEGIN

if(@fromdate != '')

begin

set @query=@query+' and cast(m.edate as date) >= cast('''+@fromdate+''' as date)'

set @query1=@query1+' and cast(m.edate as date) >= cast('''+@fromdate+''' as date)'

end

if(@todate != '')

begin

set @query=@query+' and cast(m.edate as date)<= cast('''+@todate+''' as date)'

set @query1=@query1+' and cast(m.edate as date)<= cast('''+@todate+''' as date)'

end 

END

set @query=@query+' order by m.id desc offset ('+@PageIndex+'-1)*'+@PageSize+' rows fetch next '+@PageSize+' rows only'

exec(@query)

exec sp_executesql @query1,N'@count int output',@count=@RecordCount output

END 

Angular Js Utility

1.  ng-init in Angular Js

@if (Request.QueryString["a"] != null)

{

           <input type="hidden" ng-init="p.user_type='@Request.QueryString["a"]'" />

}

                                        

2. ng-model

<input type="text" id="txtname" ng-model="p.name" class="form-control" placeholder="Enter User Address" maxlength="100" />

3.  ng-click

<input type="button" id="btnsearch" ng-click="getEmployeeList()" class="btn btn-sm btn-success" value="Search" />

4.  ng-change

<select class="form-control" ng-change="getEmployeeList()" ng-model="p.user_type" ng-init="p.user_type=''">

         <option value="">All</option>

         <option value="1">Paid</option>

         <option value="0">UnPaid</option>

</select>

5. Pass value in angular function 

 $scope.p = {};

        var status = null;

        $scope.getIncomeList = function () {

            $('.loader').show();

            if (status == null) {

                status = "2";

            }

            $http.get('/Admin/getReward_Income', $scope.p).then(function (res) {

                $('.loader').hide();

                $scope.getIncome = res.data.IncomeList;

            });

        };