Click here to Skip to main content
15,896,727 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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

This is my Store Procedure.
I want to implement the paging by using this store procedure.
please guide me.

Note i want to use this paging pattren.because i am using this in my other screens Paging GridView with ROW_NUMBER()[^].
Posted
Updated 20-Mar-13 21:45pm
v3

1 solution

Probably using "custom paging" you mean "ranking": Ranking functions (T-SQL)[^]. If yes, you need to change sql query to your needs... For example, if you want to "paging" by FirstName and LastName, change SELECT statement as is shown below:
SQL
SELECT ROW_NUMBER() OVER (ORDER BY p.FirstName + ' ' + p.LastName) AS "Row Number", p.patientid, ...


Quote:
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
.

But why reinvent the wheel? (i'm not sure i used it correctly)
Have a look here:
Table Of Contents: GridView examples for ASP.NET[^]
GridView Examples for ASP.NET 2.0: Paging and Sorting the GridView's Data
 
Share this answer
 
v2
Comments
prince_rumeel 21-Mar-13 3:20am    
My dear Friend as i mention in my question a link also.
i m using that custom paging style on my all other application part.but i need to modify my this store procedure according to that.

i need this in my application
Maciej Los 21-Mar-13 3:39am    
See my updated solution.
prince_rumeel 21-Mar-13 3:50am    
i did this last dya.but after that i was not getting way that how to solve my problem.so i revert my changes.
i mention in my question a way to do the custom paging.but i am not getting way to modify my store procedure.to get my desire result.

Dear friend plz give my the way to modify my store procedure.

i will realy thankful to u.
Maciej Los 21-Mar-13 3:59am    
If your stored procedure already exists, replace "CREATE PROCEDURE" with "ALTER PROCEDURE". That's all ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900