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;