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