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

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

评论加载中…