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

分页存储过程代码如下：
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)))'
共7页: 上一页 1 [2] [3] [4] [5] [6] [7] 下一页