UI changes for Procedure

//*******default.aspx********//

//*********Div for pagin*********//

<div style="float: left; width: 672px;" id="lblKBPaging"></div>

//*****Hidden  variables used for Paging***************//

<input type="hidden" id="hKBTotalCount" value="0" />
                    <input type="hidden" id="hKBCurrentPage" value="1" />
                    <input type="hidden" id="hKBNextPage" value="0" />
                    <input type="hidden" id="hKBPreviousPage" value="0" />


[**********abc.js**************]

// hKBTotalCount will give us Total Rows return by procedure

function abc(){
  $('#hKBTotalCount').val(result.d[0].TotalRows);

 $('#lblKBPaging').show();
                CallPaging();
}


function RowN(TotalCount) {
    var TotalP;
    TotalP = (TotalCount / KBShowRecord).toFixed(2);
    var chkTotal = TotalP - Math.floor(TotalP);
    if (chkTotal != 0) {
        TotalP = Math.floor(TotalP) + 1;
    }
    return TotalP;
}

function GetPage(V, C) {
    $('#hKBCurrentPage').val(C);
    GetAgentWiseUsage(V);
}

function Page(NextPage, Previous) {
    $('#hKBNextPage').val(NextPage);
    $('#hKBPreviousPage').val(Previous);
    CallPaging();
}

var KBShowRecord = 10;
var PagingBreak;

function CallPaging() {
    Paging($('#hKBNextPage').val(), $('#hKBCurrentPage').val(), $('#hKBPreviousPage').val())
}

function Paging(NextP, Current, Previous) {
    var NextPageNo, CurrentPage;
    var RowCount = RowN($('#hKBTotalCount').val());
    if (NextP != 0) {
        NextPageNo = NextP;
    }
    else {
        NextPageNo = 0;
    }
    if (Current != '') {
        CurrentPage = Current;
    }
    else {
        CurrentPage = 1;
    }
    var strB = '';
    var final = '';
    if (Previous == 1) {
        NextPageNo = Math.floor(Number(NextPageNo) - Number(10));
    }
    PagingBreak = Math.floor(Number(NextPageNo) + Number(10));
    if (PagingBreak > 10) {
        var iddd = Number(PagingBreak) - Number(10);
        strB += '<label id="PageBack' + iddd + '" onclick="Page(' + iddd + ',1)" style="cursor:pointer">....</label>';
        strB += '';
    }
    iLoop:
    for (var i = NextPageNo; i < RowCount; i++) {
        var txt, vcss;
        txt = (Number(i) + Number(1));
        if (txt == CurrentPage) {
            vcss = '#1d94b6';
        }
        else {
            vcss = 'Black';
        }
        var idd = (Number(i) * Number(KBShowRecord)) + Number(1);
        strB += '<label id="PageInner' + idd + '" onclick="GetPage(' + idd + ',document.getElementById(this.id).innerHTML)" style="cursor:pointer; color: ' + vcss + '">' + txt + '</label>';
        strB += '';
        if (txt == PagingBreak) {
            if (PagingBreak == RowCount) {
                final = '<div style="float: left; width: 500px; padding-left: 10px; font-family: Helvetica; font-size: 13px"><b>Page ' + CurrentPage + ' of ' + RowCount + '</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' + strB + '</div>';
                break iLoop;
            }
            strB += '<label id="PageNext' + txt + '" onclick="Page(' + txt + ',0)" style="cursor:pointer; color: ' + vcss + '">....</label>';
            final = '<div style="float: left; width: 500px; padding-left: 10px; font-family: Helvetica; font-size: 13px"><b>Page ' + CurrentPage + ' of ' + RowCount + '</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' + strB + '</div>';
            break iLoop;
        }
        final = '<div style="float: left; width: 500px; padding-left: 10px; font-family: Helvetica; font-size: 13px"><b>Page ' + CurrentPage + ' of ' + RowCount + '</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' + strB + '</div>';
    }
    $('#lblKBPaging').html(final);
}

Procedure for Paging

//*******

NOTE:
this Procedure will give us TotalCount for Paging and Row Number for paging also.

*********//

ALTER PROCEDURE [Reports].[usp_GetAgentLoginDetails] '20130113','','','','598',1,20
(
@From nvarchar(100),
@Circle varchar,
@Bpo varchar,
@Group varchar,
@tl nvarchar(50),
@start nvarchar(10),
@end nvarchar(10)
)
AS
BEGIN
DECLARE @Sql nvarchar(max)

if @Circle is null
set @Circle =''
if @Bpo is null
set @Bpo =''
if @Group is null
set @Group =''
if @tl is null
set @tl =''

SET @Sql=N'
SELECT * FROM(Select row_number() over (order by Name) as RowNum, *,count(*) Over() as ''total rows'' from(SELECT UM.EmpId,UM.Name,LD.LoginDate,UM.RMID, datediff(hh,LD.LoginDate,getdate()) ''Hours''
from SimulatorTest..UserMaster UM
INNER JOIN LoginDetails LD ON UM.ID=LD.UserID WHERE '
IF(@Circle<>'')
begin
Set @Sql=@Sql+' UM.CircleId='''+@Circle+''' AND '
End
IF(@Bpo<>'')
begin
Set @Sql=@Sql+' UM.BPOId='''+@Bpo+''' AND '
end

IF(@Group<>'')
begin
Set @Sql=@Sql+' UM.GroupId='''+@Group+''' AND '
end

 IF(@tl<>'')
 begin
Set @Sql=@Sql+' UM.RMId='+@tl+' AND '
end
Set @Sql=@Sql+' convert(datetime,convert(varchar,LD.LoginDate,101))=convert(datetime,'''+convert(varchar(15),@From,101)+'''))'
SET @Sql=@Sql+' t1)t2 WHERE RowNum BETWEEN '+@start+' AND '+@end+''

print @Sql
exec sp_executesql @Sql

--SELECT UM.EmpId,UM.Name,LD.LoginDate,UM.RMID, datediff(hh,LD.LoginDate,getdate()) 'Hours'
--from SimulatorTest..UserMaster UM
--INNER JOIN LoginDetails LD ON UM.ID=LD.UserID
--WHERE UM.CircleId=@Circle AND UM.BPOId=@Bpo AND UM.GroupId=@Group
--AND UM.RMId=@tl and
--convert(datetime,convert(varchar,LD.LoginDate,101))=convert(datetime,convert(varchar(15),@From,101))
END

//*******************Print SQL*************************//

SELECT * FROM(Select row_number() over (order by Name) as RowNum, *,count(*) Over() as 'total rows' from(SELECT UM.EmpId,UM.Name,LD.LoginDate,UM.RMID, datediff(hh,LD.LoginDate,getdate()) 'Hours'
from SimulatorTest..UserMaster UM
INNER JOIN LoginDetails LD ON UM.ID=LD.UserID WHERE  UM.RMId=598 AND  convert(datetime,convert(varchar,LD.LoginDate,101))=convert(datetime,'20130113')) t1)t2 WHERE RowNum BETWEEN 1 AND 20