partition and paging in sql server

CREATE proc [dbo].[pagingandprtion]--

(

@FilterValue VARCHAR(MAX),

@SearchText VARCHAR(MAX),

@startindex INT ,

@pagesize INT,

@Flag VARCHAR(10),

@OrderBy VARCHAR(200)

)

AS

BEGIN

DECLARE @SQL varchar(MAX)

IF(@Flag='CODE')

BEGIN

SET @SQL = '



WITH PagingCTE AS (

SELECT ROW_NUMBER()OVER(ORDER BY ItemCode)AS Row_ID1,* from

(SELECT * ,ROW_NUMBER()OVER(partition by itemcode ORDER BY itemcode)AS Filt_Id FROM vw_allProductNew1 WHERE IsActive=1 AND

(ProductCode LIKE ''%'+ @SearchText + '%'' OR ProductName LIKE ''%'+ @SearchText + '%'') '+@FilterValue+') AS A )



select * from( SELECT ROW_NUMBER()OVER(ORDER BY ItemCode)AS Row_ID,* FROM PagingCTE WHERE Filt_id=1) as a WHERE

Row_ID >= ('+cast(@pagesize as varchar(10))+' * '+cast(@startindex as varchar(10))+') - ('+cast(@pagesize as varchar(10))+' -1) AND

Row_ID <= ('+cast(@pagesize as varchar(10))+' *'+ cast(@startindex as varchar(10))+') ' +'' +@OrderBy+''

--print @sql

END

ELSE

BEGIN

SET @SQL = '



WITH PagingCTE AS (

select ROW_NUMBER()OVER(ORDER BY ItemCode)AS Row_ID1,* FROM (select ROW_NUMBER()OVER(PARTITION BY ItemCode ORDER BY ItemCode)AS ID,* FROM

(SELECT *,ROW_NUMBER()OVER(partition by itemcode ORDER BY itemcode)AS Filt_Id FROM vw_allProductNew1 WHERE

IsActive=1 AND (ProductCode LIKE ''%'+ @SearchText + '%'' OR ProductName LIKE ''%'+ @SearchText + '%'')'+@FilterValue+')AS A) B WHERE B.ID=1 )



select * from( SELECT ROW_NUMBER()OVER(ORDER BY ItemCode)AS Row_ID,* FROM PagingCTE WHERE Filt_id=1) as a WHERE

Row_ID >= ('+cast(@pagesize as varchar(10))+' * '+cast(@startindex as varchar(10))+') - ('+cast(@pagesize as varchar(10))+' -1) AND

Row_ID <= ('+cast(@pagesize as varchar(10))+' *'+ cast(@startindex as varchar(10))+') ' +@OrderBy+''

END



--print @SQL

EXEC (@SQL)

END