高级自定义查询、分页、多表联合存储过程

【 tulaoshi.com - SQLServer 】

分页存储过程代码如下：
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)  + ''' A