□分页存储过程代码如下: ALTERPRO...... 分页存储过程代码如下: 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代码如下: <%-- | | | | | | <%-- | | <%= Resources.Resource.Creator + ":"%> | <%= Resources.Resource.Changer + ":"%> 本来系统采用BLinq实现、因为有复杂的逻辑关系、在业务层联合会产生大量的查询语句(大概200~200个)、现在采用存储过程调用动态SQL效率大大提高了。希望会对朋友有所帮助和借鉴 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 11:32 , Processed in 0.095946 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.