Procedure to get datas by page index and size using sql server

create proc GridSample_Test --3,1,'Name','desc'  
(  
 @PageSize int=10,  
 @PageIndex int=1,
 @SortBy nvarchar(30)='AutoID',
 @SortOrd varchar(10)='ASC'
)  
as  
begin  

SET NOCOUNT ON

declare @orderby nvarchar(100)
declare @sqlStatement varchar(max)

set @orderby=(select case when @sortby='Name' then ' case when LastName is null then FirstName else FirstName'+'+'+'LastName end ' else @sortby end as orderby)
 
set @sqlStatement='select * from ( select Row_number() over  
  (  
  order by '+@orderby+' '+@SortOrd+'    
  ) as RowNumber,  
 AutoID  
 ,case when LastName is Null then    
  FirstName  
  else  
  FirstName+'+''' '''+'+LastName end as Name  
 ,Address1  
 ,Address2  
 ,City  
 ,State  
 ,Country  
 ,ZipCode  
 from tbl_GridSample ) as tbl where tbl.RowNumber BETWEEN (convert(int,'+convert(varchar,@PageIndex-1)+')) * convert(int,'+convert(varchar,@PageSize)+') + 1  
 AND ( ((convert(int,'+convert(varchar,@PageIndex -1)+') * convert(int,'+convert(varchar,@PageSize)+') + 1)) + convert(int,'+convert(varchar,@PageSize)+'))-1'

 --print @sqlStatement
 exec(@sqlStatement)  
end