Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET4.0
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 20-Mar-13 1:36am
Edited 20-Mar-13 22:45pm
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
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
  Permalink  
v2
Comments
prince_rumeel at 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 at 21-Mar-13 3:39am
   
See my updated solution.
prince_rumeel at 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 at 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)

  Print Answers RSS
0 OriginalGriff 365
1 Sergey Alexandrovich Kryukov 329
2 CPallini 270
3 Afzaal Ahmad Zeeshan 204
4 DamithSL 194
0 OriginalGriff 5,515
1 DamithSL 4,451
2 Maciej Los 3,902
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 21 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100