如何使用我的存储过程进行自定义分页 [英] How to do Custom Paging by using my store 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

这是我的商店程序。

我想通过以下方式实现分页使用这个商店程序。

请指导我。



注意我想要你这个分页pattren.ofause我在我的其他屏幕中使用这个 使用ROW_NUMBER（）分页GridView [ ^ ]。

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()[^].

推荐答案

可能使用自定义分页你的意思是排名：放样功能（T-SQL） [ ^ ]。如果是，您需要更改sql查询以满足您的需求...例如，如果您想通过 FirstName 和 LastName <分页 code>，更改 SELECT 语句，如下所示：



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, ...

引用：

排名函数返回a分区中每行的排名值。根据所使用的功能，某些行可能会收到与其他行相同的值。排名函数是不确定的。

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.

。



但为什么要重新发明轮子？ （我不确定我是否正确使用了它）

看看这里：

目录：ASP.NET的GridView示例 [ ^ ]

ASP.NET 2.0的GridView示例：对GridView的数据进行分页和排序

这篇关于如何使用我的存储过程进行自定义分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！