Dynamic SQL Paging

declare @PageIndex int
declare @pageSize int
declare @startindex int
declare @endindex int
declare @str varchar(1000)

set @PageIndex = 1
set @pageSize = 10

set @startindex = @pageindex
set @endindex = @pagesize


if
(@pageindex > 1)
begin
set @endindex = @pageSize * @PageIndex
set @startindex = (@endindex - @pageSize ) + 1
end

declare @whereclause varchar(100)
set @whereclause ='name like ''%health%'''

Declare @totalrows int
select @totalrows =count(company_code)
from tbl_CMT_Company

set @str='
select *, ' + cast (@totalrows as varchar) + ' as totalRecords from
(
select Company_code,name,Arabic_name,
Row_number() over (order by Company_code) as rowindex
from tbl_CMT_Company where ' + @whereclause +'
) as pageresult

where rowindex >= ' + cast (@startindex as varchar) + '
and rowindex <= ' + cast (@endindex as varchar) +' '

print @str
exec (@str)