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

分页存储过程代码如下：

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

)

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

)

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">

<atlas:ControlEventTrigger ControlID="TaskFiltButton" EventName="Click" />

<atlas:ControlEventTrigger ControlID="NewTaskFormView" EventName="ItemInserted" />

</Triggers>

<asp:DataList ID="TaskListDataList" runat="server" Width="100%" DataSourceID="TaskListDataSource">

<%--<div class="DataListDate">

</div>--%>

<asp:HyperLink ID="TaskListDetailLink" runat="server" NavigateUrl='<%# Eval("TaskID", "~/ControlPanel/WorkItem/TaskDetail.aspx?TaskID={0}") %>' Text='<%# Eval("TaskName") %>'>

</asp:HyperLink>

</div>

<asp:Label ID="PriorityLabel" runat="server" Text='<%# Eval("Priority") %>'></asp:Label>

<asp:Label ID="TaskListCompletedDateLabel" runat="server" Text='<%# Eval("CompletedDate", "{0:yyyy-MM-dd}") %>'></asp:Label>

<asp:Label ID="TaskListStateLabel" runat="server" Text='<%# Eval("State") %>'></asp:Label>

<asp:Label ID="TriageLabel" runat="server" Text='<%# Eval("Triage") %>'></asp:Label>

</div>

<asp:Literal ID="TaskListDescriptionLiteral" runat="server" Text='<%# Eval("Description") %>'></asp:Literal>

</div>

<asp:HyperLink ID="TaskListDepartmentIDLink" runat="server" NavigateUrl='<%# Eval("DepartmentID", "~/ControlPanel/DepartmentManage.aspx?DepartmentID={0}") %>' Text='<%# Eval("Department") %>'>

</asp:HyperLink>

<asp:HyperLink ID="TaskListChiefIDLink" runat="server" NavigateUrl='<%# Eval("ChiefID", "~/ControlPanel/DepartmentManage.aspx?UserID={0}") %>' Text='<%# Eval("Chief") %>'>

</asp:HyperLink>

<asp:HyperLink ID="TaskListProjectIDLink" runat="server" NavigateUrl='<%# Eval("ProjectID", "~/ControlPanel/ProjectManage.aspx?ProjectID={0}") %>' Text='<%# Eval("Project") %>'>

</asp:HyperLink>

<asp:HyperLink ID="TaskListProjectAreaIDLink" runat="server" NavigateUrl='<%# Eval("ProjectAreaID", "~/ControlPanel/ProjectManage.aspx?ProjectAreaID={0}") %>' Text='<%# Eval("ProjectArea") %>'>

</asp:HyperLink>

<%-- | 

<asp:HyperLink ID="TaskListParentIDLink" runat="server" NavigateUrl='<%# Eval("ParentID", "~/TaskDetail.aspx?TaskID={0}") %>' Text='<%# Eval("Parent.TaskName") %>'>

</asp:HyperLink>--%>

<asp:Label ID="TaskListPlanStartDatePlanEndDateLabel" runat="server" Text='<%# "(" + Eval("PlanStartDate", "{0:yyyy-MM-dd}") + "~" + Eval("PlanEndDate", "{0:yyyy-MM-dd}") + ")" %>'></asp:Label>

</div>

<%= 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>

<%= 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>

</ItemTemplate>

</asp:DataList>

<asp:ObjectDataSource ID="TaskListDataSource" runat="server" DataObjectTypeName="AIO.WITDB.WITDataObject" SelectMethod="ReadPagedAndSorted" TypeName="AIO.WITDB.WITDataObject">

<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