高级自定义查询、分页、多表联合存储过程_数据库技巧

分页存储过程代码如下：
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效率大大提高了。希望会对朋友有所帮助和借鉴
http://www.cnblogs.com/Bolik/archive/2006/08/24/485647.html