Very usefull staff !
paging example :
with searchCTE as
(
selectCode,
Descript,
ROW_NUMBER() over ( order by Code ) as RowNoCode,
ROW_NUMBER() over ( order by CAST(Code as BIGINT)) as RowNoCodeNum,
ROW_NUMBER() over ( order by Descript ) as RowNoDescript
from #tblData
where--Do SEARCH
( @pSearchBy in ( 0, 2 ) and Code like @sTEMP )
or
( @pSearchBy in ( 1, 2 ) and Descript like @sTEMP )
)
select Code,
Descript
from
( select Code,
Descript,
case
when @pPagingBy = 0 and @pElemName = 'PermProfile' then RowNoCodeNum-- Paging by CodeNum
when @pPagingBy = 0 then RowNoCode-- Paging by Code
else RowNoDescript-- Paging by Descript
end as RowNo
from searchCTE
) T1
where@pPageNo = 0
or
( @pPageNo > 0 and
RowNo between (@pPageNo-1)*@pPageSize + 1 and @pPageNo*@pPageSize
)
order by RowNo;