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

分页存储过程代码如下：

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代码如下：

< atlas:UpdatePanel  ID ="TaskListUpdatePanel"  runat ="server"  Mode ="Conditional" >
       < Triggers >
         < atlas:ControlEventTrigger  ControlID ="TaskFiltButton"  EventName ="Click"   />
         < atlas:ControlEventTrigger  ControlID ="NewTaskFormView"  EventName ="ItemInserted"   />
       </ Triggers >
       < ContentTemplate >
         < asp:DataList  ID ="TaskListDataList"  runat ="server"  Width ="100%"  DataSourceID ="TaskListDataSource" >
           < ItemTemplate >
             <% --<div class="DataListDate">
            </div>-- %>
             < div  class ="DataListItem" >
               < div  class ="DataListTitle" >
                 < asp:HyperLink  ID ="TaskListDetailLink"  runat ="server"  NavigateUrl ='<%#  Eval("TaskID", "~/ControlPanel/WorkItem/TaskDetail.aspx?TaskID ={0}")  % > ' Text=' <% # Eval("TaskName")  %> '>
                 </ asp:HyperLink >
               </ div >
               < div  class ="DataListStatus" >
                 < asp:Label  ID ="PriorityLabel"  runat ="server"  Text ='<%#  Eval("Priority") % > '> </ asp:Label >
                 &nbsp; | &nbsp;
                 < asp:Label  ID ="TaskListCompletedDateLabel"  runat ="server"  Text ='<%#  Eval("CompletedDate", "{0:yyyy-MM-dd}") % > '> </ asp:Label >
                 &nbsp; | &nbsp;
                 < asp:Label  ID ="TaskListStateLabel"  runat ="server"  Text ='<%#  Eval("State") % > '> </ asp:Label >
                 &nbsp; | &nbsp;
                 < asp:Label  ID ="TriageLabel"  runat ="server"  Text ='<%#  Eval("Triage") % > '> </ asp:Label >
               </ div >
               < div  class ="DataListBody" >
                 < asp:Literal  ID ="TaskListDescriptionLiteral"  runat ="server"  Text ='<%#  Eval("Description") % > '> </ asp:Literal >
               </ div >
               < div  class ="DataListFoot" >
                 < asp:HyperLink  ID ="TaskListDepartmentIDLink"  runat ="server"  NavigateUrl ='<%#  Eval("DepartmentID", "~/ControlPanel/DepartmentManage.aspx?DepartmentID ={0}")  % > ' Text=' <% # Eval("Department")  %> '>
                 </ asp:HyperLink >
                 &nbsp; | &nbsp;
                 < asp:HyperLink  ID ="TaskListChiefIDLink"  runat ="server"  NavigateUrl ='<%#  Eval("ChiefID", "~/ControlPanel/DepartmentManage.aspx?UserID ={0}")  % > ' Text=' <% # Eval("Chief")  %> '>
                 </ asp:HyperLink >
                 &nbsp; | &nbsp;
                 < asp:HyperLink  ID ="TaskListProjectIDLink"  runat ="server"  NavigateUrl ='<%#  Eval("ProjectID", "~/ControlPanel/ProjectManage.aspx?ProjectID ={0}")  % > ' Text=' <% # Eval("Project")  %> '>
                 </ asp:HyperLink >
                 &nbsp; | &nbsp;
                 < asp:HyperLink  ID ="TaskListProjectAreaIDLink"  runat ="server"  NavigateUrl ='<%#  Eval("ProjectAreaID", "~/ControlPanel/ProjectManage.aspx?ProjectAreaID ={0}")  % > ' Text=' <% # Eval("ProjectArea")  %> '>
                 </ asp:HyperLink >
                 <% --&nbsp;|&nbsp;
                <asp:HyperLink ID="TaskListParentIDLink" runat="server" NavigateUrl='<%# Eval("ParentID", "~/TaskDetail.aspx?TaskID={0}") %>' Text='<%# Eval("Parent.TaskName") %>'>
                </asp:HyperLink>-- %>
                 &nbsp; | &nbsp;
                 < asp:Label  ID ="TaskListPlanStartDatePlanEndDateLabel"  runat ="server"  Text ='<%#  "(" + Eval("PlanStartDate", "{0:yyyy-MM-dd}") + "~" + Eval("PlanEndDate", "{0:yyyy-MM-dd}") + ")" % > '> </ asp:Label >
               </ div >
               < div  class ="DataListVersion" >
                 <% =  Resources.Resource.Creator  +   " ： " %>
                 < asp:Label  ID ="CreatorLabel"  runat ="server"  Text ='<%#  Eval("Creator") % > '> </ asp:Label >
                 < asp:Label  ID ="CreatedDateLabel"  runat ="server"  Text ='<%#  Eval("CreatedDate", "{0:yyyy-MM-dd hh:mm:ss}") % > '> </ asp:Label >
                 &nbsp; | &nbsp;
                 <% =  Resources.Resource.Changer  +   " ： " %>
                 < asp:Label  ID ="ChangerLabel"  runat ="server"  Text ='<%#  Eval("Changer") % > '> </ asp:Label >
                 < asp:Label  ID ="ChangedDateLabel"  runat ="server"  Text ='<%#  Eval("ChangedDate", "{0:yyyy-MM-dd hh:mm:ss}") % > '> </ asp:Label >
               </ div >
             </ div >
           </ ItemTemplate >
         </ asp:DataList >
         < asp:ObjectDataSource  ID ="TaskListDataSource"  runat ="server"  DataObjectTypeName ="AIO.WITDB.WITDataObject"  SelectMethod ="ReadPagedAndSorted"  TypeName ="AIO.WITDB.WITDataObject" >
           < SelectParameters >
             < asp:ControlParameter  ControlID ="TaskFiltProjectList"  Name ="projectID"  PropertyName ="SelectedValue"   />
             < asp:ControlParameter  ControlID ="TaskFiltProjectAreaTextBox"  Name ="projectAreaID"  PropertyName ="Value"   />
             < asp:ControlParameter  ControlID ="TaskFiltDepartmentTextbox"  Name ="departmentID"  PropertyName ="Value"   />
             < asp:ControlParameter  ControlID ="TaskFiltChiefList"  Name ="ChiefID"  PropertyName ="SelectedValue"   />
             < asp:ControlParameter  ControlID ="TaskFiltStateSelectOptionDropDownList"  Name ="state"  PropertyName ="SelectOptionItem"  Type ="String"   />
             < asp:ControlParameter  ControlID ="TaskFiltPrioritySelectOptionDropDownList"  Name ="priority"  PropertyName ="SelectOptionItem"  Type ="String"   />
             < asp:ControlParameter  ControlID ="TaskFiltTriageSelectOptionDropDownList"  Name ="triage"  PropertyName ="SelectOptionItem"  Type ="String"   />
             < asp:ControlParameter  ControlID ="TaskFiltPlanStartDateFDateTextBox"  Name ="planStartDateF"  PropertyName ="Text"  Type ="DateTime"   />
             < asp:ControlParameter  ControlID ="TaskFiltPlanStartDateLDateTextBox"  Name ="planStartDateL"  PropertyName ="Text"  Type ="DateTime"   />
             < asp:ControlParameter  ControlID ="TaskFiltPlanEndDateFDateTextBox"  Name ="planEndDateF"  PropertyName ="Text"  Type ="DateTime"   />
             < asp:ControlParameter  ControlID ="TaskFiltPlanEndDateLDateTextBox"  Name ="planEndDateL"  PropertyName ="Text"  Type ="DateTime"   />
             < asp:ControlParameter  ControlID ="TaskFiltCompletedDateFDateTextBox"  Name ="completedDateF"  PropertyName ="Text"  Type ="DateTime"   />
             < asp:ControlParameter  ControlID ="TaskFiltCompletedDateLDateTextBox"  Name ="completedDateL"  PropertyName ="Text"  Type ="DateTime"   />
             < asp:Parameter  ConvertEmptyStringToNull ="True"  DefaultValue =""  Name ="sortExpression"  Type ="String"   />
             < asp:Parameter  ConvertEmptyStringToNull ="True"  DefaultValue ="0"  Name ="startRowIndex"   />
             < asp:Parameter  ConvertEmptyStringToNull ="True"  DefaultValue ="20"  Name ="maximumRows"   />
           </ SelectParameters >
         </ asp:ObjectDataSource >
       </ ContentTemplate >
     </ atlas:UpdatePanel >

        本来系统采用BLinq实现、因为有复杂的逻辑关系、在业务层联合会产生大量的查询语句（大概200~200个）、现在采用存储过程调用动态SQL效率大大提高了。希望会对朋友有所帮助和借鉴

转载于:https://www.cnblogs.com/Bolik/archive/2006/08/24/485647.html