高级自定义查询、分页、多表联合存储过程,高级自定义查询、分页、多表联合存储过程
分页存储过程代码如下：
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 = 0000000-0000-0000-0000-000000000000))
SET @WhereClause = @WhereClause + AND
([ProjectID] = \ + CAST(@ProjectID as nvarchar(64)) + \)
if not ((@ProjectAreaID is null) or (@ProjectAreaID = 0000000-0000-0000-0000-000000000000))
SET @WhereClause = @WhereClause + AND
([ProjectAreaID] = \ + CAST(@ProjectAreaID as nvarchar(64)) + \)
if not ((@DepartmentID is null) or (@DepartmentID = 0000000-0000-0000-0000-000000000000))
SET @WhereClause = @WhereClause + AND
([DepartmentID] = \ + CAST(@DepartmentID as nvarchar(64)) + \)
if not ((@ChiefID is null) or (@ChiefID = 0000000-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) + \ A
