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

 
[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)))'
共7页: 上一页 [1] [2] 3 [4] [5] [6] [7] 下一页

评论加载中…