Go homepage(回首页)
Upload pictures (上传图片)
Write articles (发文字帖)

The author:(作者)归海一刀
published in(发表于) 2014/2/3 6:48:48
高级自定义查询、分页、多表联合存储过程_[SQL Server教程]

分页存储过程代码如下：
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代码如下：








<%--

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

来源:网络

赞