mysql 多表 分页存储过程,高级自定义查询、分页、多表联合存储过程_数据库技巧...

分页存储过程代码如下：

ALTER PROCEDURE [dbo].[Task_SelectPagedAndSorted]

(

@ProjectID uniqueidentifier,

@ProjectAreaID uniqueidentifier,

@DepartmentID uniqueidentifier,

@ChiefID uniqueidentifier,

@State nvarchar(32),

@Priority int,

@Triage nvarchar(32),

@PlanStartDateF datetime,

@PlanStartDateL datetime,

@PlanEndDateF datetime,

@PlanEndDateL datetime,

@CompletedDateF datetime,

@CompletedDateL datetime,

@SortExpression nvarchar(256),

@StartRowIndex int,

@MaximumRows int

)

AS

DECLARE @sql nvarchar(4000)

DECLARE @ViewSql nvarchar(4000)

DECLARE @WhereClause nvarchar(2000)

DeCLARE @FEndRowIndex int

DeCLARE @FStartRowIndex int

DeCLARE @FMaximumRows int

DeCLARE @FSortExpression nvarchar(256)

— Make sure a @sortExpression is specified

IF LEN(@SortExpression) > 0

SET @FSortExpression = @SortExpression

ELSE

SET @FSortExpression = ChangedDate DESC

if (@StartRowIndex is null)

SET @FStartRowIndex = 0;

else

SET @FStartRowIndex = @StartRowIndex

if (@MaximumRows is null) or (@MaximumRows <= 0)

SET @FMaximumRows = 1000;

else

SET @FMaximumRows = @MaximumRows

SET @FEndRowIndex = @FStartRowIndex + @FMaximumRows

SET @WhereClause = WHERE —

if not ((@ProjectID is null) or (@ProjectID = 00000000-0000-0000-0000-000000000000))

SET @WhereClause = @WhereClause + AND

([ProjectID] = + CAST(@ProjectID as nvarchar(64)) + )

if not ((@ProjectAreaID is null) or (@ProjectAreaID = 00000000-0000-0000-0000-000000000000))

SET @WhereClause = @WhereClause + AND

([ProjectAreaID] = + CAST(@ProjectAreaID as nvarchar(64)) + )

if not ((@DepartmentID is null) or (@DepartmentID = 00000000-0000-0000-0000-000000000000))

SET @WhereClause = @WhereClause + AND

([DepartmentID] = + CAST(@DepartmentID as nvarchar(64)) + )

if not ((@ChiefID is null) or (@ChiefID = 00000000-0000-0000-0000-000000000000))

SET @WhereClause = @WhereClause + AND

([ChiefID] = + CAST(@ChiefID as nvarchar(64)) + )

if  LEN(@State) > 0

SET @WhereClause = @WhereClause + AND

([State] = + @State + )

if not ((@Priority is null) or (@Priority < 0))

SET @WhereClause = @WhereClause + AND

([Priority] = + CONVERT(nvarchar(10), @Priority) + )

if  LEN(@Triage) > 0

SET @WhereClause = @WhereClause + AND

([Triage] = + @Triage + )

if not (@PlanStartDateF is null)

SET @WhereClause = @WhereClause + AND

(([PlanStartDate] is null) or ([PlanStartDate] >= CAST( + CAST(@PlanStartDateF as nvarchar)  + AS datetime)))

if not (@PlanStartDateL is null)

SET @WhereClause = @WhereClause + AND

(([PlanStartDate] is null) or ([PlanStartDate] <= CAST( + CAST(@PlanStartDateL as nvarchar)  + AS datetime)))

if not (@PlanEndDateF is null)

SET @WhereClause = @WhereClause + AND

(([PlanEndDate] is null) or ([PlanEndDate] >= CAST( + CAST(@PlanEndDateF as nvarchar)  + AS datetime)))

if not (@PlanEndDateL is null)

SET @WhereClause = @WhereClause + AND

(([PlanEndDate] is null) or ([PlanEndDate] <= CAST( + CAST(@PlanEndDateL as nvarchar)  + AS datetime)))

if not (@CompletedDateF is null)

SET @WhereClause = @WhereClause + AND

(([CompletedDate] is null) or ([CompletedDate] >= CAST( + CAST(@CompletedDateF as nvarchar)  + AS datetime)))

if not (@CompletedDateL is null)

SET @WhereClause = @WhereClause + AND

(([CompletedDate] is null) or ([CompletedDate] <= CAST( + CAST(@CompletedDateL as nvarchar)  + AS datetime)))

if (@WhereClause = WHERE –)

SET @WhereClause =

SET @sql =

SELECT

Task.[TaskID],

[TaskSQN],

[TaskName],

[DepartmentID],

[ChangerID],

[CreatedDate],

(SELECT FullName FROM dbo.UserInfo AS CreatorUser WHERE (dbo.Task.CreatorID = UserID)) AS

Creator,

[CreatorID],

[Triage],

(SELECT DepartmentName FROM dbo.Department WHERE (dbo.Task.DepartmentID = DepartmentID)) AS

Department,

[ChiefID],

(SELECT FullName FROM dbo.UserInfo AS ChiefUser WHERE (dbo.Task.ChiefID = UserID)) AS

Chief,

[ProjectID],

(SELECT ProjectName FROM dbo.Project WHERE (dbo.Task.ProjectID = ProjectID)) AS

Project,

[PlanEndDate],

[PlanStartDate],

[CompletedDate],

[Priority],

[State],

[WorkLoad],

(SELECT TaskName FROM dbo.Task AS ParentTask WHERE (dbo.Task.ParentID = TaskID)) AS

ParentTask,

[ParentID],

(SELECT ProjectAreaName FROM dbo.ProjectArea WHERE (dbo.Task.ProjectAreaID = ProjectAreaID)) AS

ProjectArea,

[ProjectAreaID],

[Description],

[Rev],

[ChangedDate],

(SELECT FullName FROM dbo.UserInfo AS ChangerUser WHERE (dbo.Task.ChangerID = UserID)) AS

Changer

FROM Task,

(SELECT

[TaskID],

ROW_NUMBER() OVER (ORDER BY + @FSortExpression + ) AS RowRank

FROM [Task]

+ @WhereClause +

) AS RankTask

WHERE (Task.TaskID = RankTask.TaskID)

AND (RankTask.RowRank >= + CONVERT(nvarchar(10), @FStartRowIndex) + )

AND (RankTask.RowRank < + CONVERT(nvarchar(10), @FEndRowIndex) + )

SET @ViewSql =

SELECT

ViewTask.[TaskID],

[TaskSQN],

[TaskName],

[DepartmentID],

[ChangerID],

[CreatedDate],

[Creator],

[CreatorID],

[Triage],

[Department],

[ChiefID],

[Chief],

[ProjectID],

[Project],

[PlanEndDate],

[PlanStartDate],

[CompletedDate],

[Priority],

[State],

[WorkLoad],

[ParentTask],

[ParentID],

[ProjectArea],

[ProjectAreaID],

[Description],

[Rev],

[ChangedDate],

[Changer]

FROM ViewTask,

(SELECT

[TaskID],

ROW_NUMBER() OVER (ORDER BY + @FSortExpression + ) AS RowRank

FROM [Task]

+ @WhereClause +

) AS RankTask

WHERE (ViewTask.TaskID = RankTask.TaskID)

AND (RankTask.RowRank >= + CONVERT(nvarchar(10), @FStartRowIndex) + )

AND (RankTask.RowRank < + CONVERT(nvarchar(10), @FEndRowIndex) + )

EXEC sp_executesql @sql

RETURN

计算Count代码如下：

ALTER PROCEDURE dbo.Task_SelectPagedAndSortedCount

(

@ProjectID uniqueidentifier,

@ProjectAreaID uniqueidentifier,

@DepartmentID uniqueidentifier,

@ChiefID uniqueidentifier,

@State nvarchar(32),

@Priority int,

@Triage nvarchar(32),

@PlanStartDateF datetime,

@PlanStartDateL datetime,

@PlanEndDateF datetime,

@PlanEndDateL datetime,

@CompletedDateF datetime,

@CompletedDateL datetime,

@Count int output

)

AS

DECLARE @sql nvarchar(4000)

DECLARE @WhereClause nvarchar(2000)

SET @WhereClause = WHERE —

if not (@ProjectID is null)

SET @WhereClause = @WhereClause + AND

([ProjectID] = CAST( + CAST(@ProjectID as nvarchar) + ) AS uniqueidentifier)

if not (@ProjectAreaID is null)

SET @WhereClause = @WhereClause + AND

([ProjectAreaID] = CAST( + CAST(@ProjectAreaID as nvarchar) + ) AS uniqueidentifier)

if not (@DepartmentID is null)

SET @WhereClause = @WhereClause + AND

([DepartmentID] = CAST( + CAST(@DepartmentID as nvarchar) + ) AS uniqueidentifier)

if not (@ChiefID is null)

SET @WhereClause = @WhereClause + AND

([ChiefID] = CAST( + CAST(@ChiefID as nvarchar) + ) AS uniqueidentifier)

if  LEN(@State) > 0

SET @WhereClause = @WhereClause + AND

([State] = + @State + )

if not ((@Priority is null) or (@Priority < 0))

SET @WhereClause = @WhereClause + AND

([Priority] = + CONVERT(nvarchar(10), @Priority) + )

if  LEN(@Triage) > 0

SET @WhereClause = @WhereClause + AND

([Triage] = + @Triage + )

if not (@PlanStartDateF is null)

SET @WhereClause = @WhereClause + AND

(([PlanStartDate] is null) or ([PlanStartDate] >= CAST( + CAST(@PlanStartDateF as nvarchar)  + AS datetime)))

if not (@PlanStartDateL is null)

SET @WhereClause = @WhereClause + AND

(([PlanStartDate] is null) or ([PlanStartDate] <= CAST( + CAST(@PlanStartDateL as nvarchar)  + AS datetime)))

if not (@PlanEndDateF is null)

SET @WhereClause = @WhereClause + AND

(([PlanEndDate] is null) or ([PlanEndDate] >= CAST( + CAST(@PlanEndDateF as nvarchar)  + AS datetime)))

if not (@PlanEndDateL is null)

SET @WhereClause = @WhereClause + AND

(([PlanEndDate] is null) or ([PlanEndDate] <= CAST( + CAST(@PlanEndDateL as nvarchar)  + AS datetime)))

if not (@CompletedDateF is null)

SET @WhereClause = @WhereClause + AND

(([CompletedDate] is null) or ([CompletedDate] >= CAST( + CAST(@CompletedDateF as nvarchar)  + AS datetime)))

if not (@CompletedDateL is null)

SET @WhereClause = @WhereClause + AND

(([CompletedDate] is null) or ([CompletedDate] <= CAST( + CAST(@CompletedDateL as nvarchar)  + AS datetime)))

if (@WhereClause = WHERE –)

SET @WhereClause =

SET @sql = (

SELECT

+ @Count + = Count(*)

FROM [Task]

+ @WhereClause + )

— Execute the SQL query

EXEC sp_executesql @sql

RETURN

DataList代码如下：

–%>