Custom Paging Stored Procedure

CREATE PROCEDURE [dbo].[sp_GetPatientsEvaluation]
	-- Add the parameters for the stored procedure here
	@PFirstName as nvarchar(max),
	@PLastName as nvarchar(max),
	@StartDate as DateTime=null,
	@EndDate as DateTime=null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @strSelectQry nvarchar(max)
	Declare @where_clause NVARCHAR(1000)
	set @where_clause=' where FirstName like ''%'+@PFirstName+'%''	 AND  LastName like ''%'+@PLastName+'%'''
	if @StartDate is not null
	begin  
		set @where_clause=@where_clause + ' and pe.EvaluationDate >= ''' + Convert(varchar(10),@StartDate,101) + ''''
	end	
	if @EndDate is not null
	begin  
		set @where_clause=@where_clause + ' and pe.EvaluationDate <= ''' + Convert(varchar(10),@EndDate,101) + ''''
	end
	
	
	set @strSelectQry = 'SELECT	p.patientid,p.FirstName + ''  '' + p.LastName as ''PName'',p.DateofBirth,isnull(pe.PatientEvaluationInformationID,0) as ''PatientEvaluationInformationID'',pe.EvaluationDate,
			pe.EvaluationStartTime,pe.EvaluationLength,pe.EvaluationEndTime,pe.SourceOfInformation,pe.EvaluatedBy,
			(Select top 1 PatientEvaluationInformationID from PatientEvaluationInformation 
				where PatientEvaluationInformation.Patientid = p.patientid and PatientEvaluationInformation.EvaluationType =1
				order by EvaluationDate Desc) as PatientFollowUpEvaluationInformationID,
			(Select count(EvaluationDate) from PatientEvaluationInformation where PatientEvaluationInformation.Patientid = p.patientid 
				and PatientEvaluationInformation.EvaluationType =1) as TCount,
			(Select top 1 EvaluationDate from PatientEvaluationInformation 
				where PatientEvaluationInformation.Patientid = p.patientid and PatientEvaluationInformation.EvaluationType =1
				order by EvaluationDate Desc) as LatestDate
	FROM patients p 
	LEFT OUTER JOIN
	PatientEvaluationInformation pe on p.patientid =pe.patientID and pe.EvaluationType =0'
	
	exec (@strSelectQry + @where_clause + ' order By p.patientid DESC')
END

set rowcount @MaxRows;

select
     p_Id               as 'ProductId'
    ,p_Name             as 'ProductName'
    ,p_Description      as 'Description'
    ,p_Quantity         as 'Quantity'
    ,p_Price            as 'Price'
from
(
    select 
        row_number() over 
        (
            order by case quotename(@OrderByField )
                when '[ProductName]' then p.p_name
                when '[Description]' then p.p_Description
            end)     as RowNumber
        ,p.p_Id
        ,p.p_Name
        ,p.p_Description
        ,p.p_Quantity
        ,p.p_Price
    from dbo.Products as p
    where @SearchValue like case quotename(@SearchField)
        when '[ProductName]' then '%' + p.p_Name + '%'
        when '[Quantity]' then convert(nvarchar(10), p.p_Quantity)
        when '[Price]' then convert(nvarchar(10), p.p_Price)
    end
) as w
where RowNumber >= @StartRow;

DECLARE @searchValue AS VARCHAR(200)

-- complete the query in the stored procedure before the searchValue
SET @searchValue = '10) AS [t1];'

-- add an insert query to the searchvalue
SET @searchValue = @searchValue + 'INSERT INTO Products VALUES (NEWID(), ''T'', ''T'', 1, 1.0);'

-- complete the query in the stored procedure after the searchValue
SET @searchValue = @searchValue + 'select p_Id ProductId,'''' ProductName,'''' Description,1 Quantity,1 Price FROM (select *, 1 as ROW_NUMBER from Products'

EXEC GetProducts 0, 1000, 'ProductName', 1, 'Quantity', @searchValue

SELECT * FROM Products WHERE p_Name = 'T'

EXEC sp_Executesql @Query,
	N'@OrderQuery nvarchar(max),
 	@StartRowIndex int,@MaxRows int',
	@OrderQuery=@OrderQuery,
	@StartRowIndex = @StartRowIndex,
	@MaxRows = @MaxRows