SQL 复杂的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22500454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Complex stored procedure
提问by Carel
I am a novice with regards to sql and I'm trying to create a rather complex stored procedure that is to be used by a report created using sql reporting services within Business Intelligence of Visual Studio.
我是 sql 方面的新手，我正在尝试创建一个相当复杂的存储过程，该过程将由在 Visual Studio 的商业智能中使用 sql 报告服务创建的报告使用。
I have a main 'Project' table that is linked various other tables by making use of link tables (the ones of interest in my stored procedure being 'Status', 'Facility' and 'Branch'). What the application does is a researcher submits a project and it goes through the following approval process:
我有一个主要的“项目”表，它通过使用链接表（在我的存储过程中感兴趣的是“状态”、“设施”和“分支”）来链接各种其他表。申请的作用是研究人员提交一个项目，并经过以下审批流程：
Specialised Health Manager > Branch Head > Health Management Team
专业健康经理 > 分公司负责人 > 健康管理团队
To facilitate this approval process, another team member developed a custom Workflow solution that makes use of 'WorkflowHistory' table. What gets put in this table (among others) is the ProjectId, the WorkflowStep and the Date the workflow step was performed and a comment issued by the person who performed the step. What I want in my report is the following:
为促进此审批流程，另一名团队成员开发了一个自定义工作流解决方案，该解决方案使用了“WorkflowHistory”表。放入该表（以及其他）的是 ProjectId、WorkflowStep 和执行工作流步骤的日期以及执行该步骤的人员发布的评论。我在报告中想要的是以下内容：
The info I want in my report is the following:
我在报告中想要的信息如下：
The 'ProjectId' and 'Title' come from the 'Project' table. The 'Facilities', 'Branch' and 'Status' com from the tables linked to 'Project'. 'Recieved' needs to be the date the initial workflow step was performed for the project. The 'Concluded' needs to be the date the final workflow step was performed for the project. The 'Comment' needs to be the comment that was left for the final workflow step.
“ProjectId”和“Title”来自“Project”表。来自链接到“项目”的表格中的“设施”、“分支”和“状态”com。“已收到”必须是为项目执行初始工作流程步骤的日期。“结束”需要是为项目执行最终工作流程步骤的日期。'Comment' 需要是为最后的工作流程步骤留下的评论。
So, the data for each line in the report is to come from the following places:
因此，报表中每一行的数据都来自以下位置：
- one line from 'Project'
- one line from 'Branch'
- one line from 'Status'
- multiple lines from 'Facility' (below on my running attempt I'm only returning the first facility from the sub-query, but I want all facilities assigned to the project, comma-space delimited)
- two different lines from 'WorkflowHistory'
- “项目”中的一行
- 'Branch' 中的一行
- “状态”中的一行
- 来自“设施”的多行（在我的运行尝试下方，我只从子查询中返回第一个设施，但我希望将所有设施分配给项目，以逗号分隔）
- 来自“WorkflowHistory”的两条不同线
The user passes the following parameters to filter the report:
用户传递以下参数来过滤报告：
- From Date - to get all reports received after a specific date (this will be the 'ActionedOn' for the first workflow step in 'WorkflowHistory')
- To Date - to get all reports received before a specific date (this will be the 'ActionedOn' for the final workflow step in 'WorkflowHistory')
- Status - Filter projects with a specific status
- Branch - Filter projects assigned to a specific branch
- 从日期 - 获取在特定日期之后收到的所有报告（这将是“WorkflowHistory”中第一个工作流程步骤的“ActionedOn”）
- 迄今为止 - 获取在特定日期之前收到的所有报告（这将是“WorkflowHistory”中最后一个工作流程步骤的“ActionedOn”）
- 状态 - 筛选具有特定状态的项目
- 分支 - 过滤分配给特定分支的项目
I have tried to accomplish all of this with the below stored procedure. This is my running attempt, that I'm continually working on. The issues I'm still having include the following:
我试图用下面的存储过程来完成所有这些。这是我的跑步尝试，我一直在努力。我仍然遇到的问题包括：
- The date range filtering doesn't work
- The I can only return the first facility in the sub-query (following is the issues still existing when commenting out the entire WHERE portion)
- The final workflow step of the project might be anything between 2 and 5, depending on whether it was approved and when it was rejected. I need to figure out how to get the 'Concluded' date as well as the Comment left on this step.
- I pass the 'Status' in a parameter. I need to figure out how to filer by one status or by all (which is not actually all the statuses, but the final 3 being 'Approved', 'Declined' and 'Concluded'). Same for branch.
- 日期范围过滤不起作用
- I 只能返回子查询中的第一个工具（以下是注释掉整个 WHERE 部分时仍然存在的问题）
- 项目的最终工作流程步骤可能介于 2 和 5 之间，具体取决于它是否被批准以及何时被拒绝。我需要弄清楚如何获得“结束”日期以及这一步留下的评论。
- 我在参数中传递“状态”。我需要弄清楚如何按一种状态或所有状态提交（这实际上不是所有状态，但最后 3 种状态是“已批准”、“已拒绝”和“已结束”）。分行也一样。
Edit: It is now what, 5 hours later and I've updated the stored procedure below. I've sorted most of the issues out by making use of a temporary table variable
编辑：现在是什么，5 小时后，我更新了下面的存储过程。我已经通过使用临时表变量解决了大部分问题
CREATE PROCEDURE [dbo].[stp_CityHealthResearchRequestsReport]
@FromDate DATETIME,
@ToDate DATETIME,
@StatusId int,
@BranchId int,
@Count INT OUTPUT
AS
BEGIN
DECLARE @TempTable TABLE
(
ProjectId INT,
Recieved DATETIME,
Concluded DATETIME,
Comment VARCHAR(8000)
)
IF @StatusId <> 0 AND @BranchId <> 0
BEGIN
INSERT INTO @TempTable (ProjectId, Recieved, Concluded, Comment)
SELECT DISTINCT
p.ProjectId,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 1
ORDER BY wf.WorkflowHistoryId DESC) AS Recieved,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Concluded,
(SELECT TOP 1 wf.Comment
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Comment
FROM
Project p
JOIN WorkflowHistory w ON p.ProjectId = w.ProjectId
JOIN ProjectBranch pb ON pb.ProjectId = p.ProjectId
WHERE
p.ProjectId = w.ProjectId
AND p.StatusId = @StatusId
AND pb.BranchId = @BranchId
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
SELECT DISTINCT
p.ProjectId,
p.Title,
STUFF (
(SELECT ', ' + f.Name
FROM dbo.Facility f
LEFT JOIN dbo.ProjectFacility pf ON f.FacilityId = pf.FacilityId
WHERE pf.ProjectId = p.ProjectId
FOR XML PATH (''))
, 1, 1, '') AS Facilities,
tt.Recieved,
tt.Concluded,
b.BranchName,
st.Description AS StatusText,
tt.Comment,
tt.Concluded - tt.Recieved AS Turnaround
FROM
dbo.Project p
INNER JOIN @TempTable tt ON p.ProjectId = tt.ProjectId
LEFT JOIN dbo.ProjectBranch pb ON p.ProjectId = pb.ProjectId
LEFT JOIN dbo.Branch b ON pb.BranchId = b.BranchId
LEFT JOIN dbo.Status st ON p.StatusId = st.StatusId
WHERE
p.StatusId = @StatusId
AND b.BranchId = @BranchId
SET @Count = @@ROWCOUNT
END
IF @StatusId <> 0 AND @BranchId = 0
BEGIN
INSERT INTO @TempTable (ProjectId, Recieved, Concluded, Comment)
SELECT DISTINCT
p.ProjectId,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 1
ORDER BY wf.WorkflowHistoryId DESC) AS Recieved,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Concluded,
(SELECT TOP 1 wf.Comment
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Comment
FROM
Project p
JOIN WorkflowHistory w ON p.ProjectId = w.ProjectId
JOIN ProjectBranch pb ON pb.ProjectId = p.ProjectId
WHERE
p.ProjectId = w.ProjectId
AND p.StatusId = @StatusId
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
SELECT DISTINCT
p.ProjectId,
p.Title,
STUFF (
(SELECT ', ' + f.Name
FROM dbo.Facility f
LEFT JOIN dbo.ProjectFacility pf ON f.FacilityId = pf.FacilityId
WHERE pf.ProjectId = p.ProjectId
FOR XML PATH (''))
, 1, 1, '') AS Facilities,
tt.Recieved,
tt.Concluded,
b.BranchName,
st.Description AS StatusText,
tt.Comment,
tt.Concluded - tt.Recieved AS Turnaround
FROM
dbo.Project p
INNER JOIN @TempTable tt ON p.ProjectId = tt.ProjectId
LEFT JOIN dbo.ProjectBranch pb ON p.ProjectId = pb.ProjectId
LEFT JOIN dbo.Branch b ON pb.BranchId = b.BranchId
LEFT JOIN dbo.Status st ON p.StatusId = st.StatusId
WHERE
p.StatusId = @StatusId
SET @Count = @@ROWCOUNT
END
IF @StatusId = 0 AND @BranchId <> 0
BEGIN
INSERT INTO @TempTable (ProjectId, Recieved, Concluded, Comment)
SELECT DISTINCT
p.ProjectId,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 1
ORDER BY wf.WorkflowHistoryId DESC) AS Recieved,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Concluded,
(SELECT TOP 1 wf.Comment
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Comment
FROM
Project p
JOIN WorkflowHistory w ON p.ProjectId = w.ProjectId
JOIN ProjectBranch pb ON pb.ProjectId = p.ProjectId
WHERE
p.ProjectId = w.ProjectId
AND p.StatusId = 5
AND pb.BranchId = @BranchId
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
OR p.ProjectId = w.ProjectId
AND p.StatusId = 6
AND pb.BranchId = @BranchId
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
OR p.ProjectId = w.ProjectId
AND p.StatusId = 7
AND pb.BranchId = @BranchId
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
SELECT DISTINCT
p.ProjectId,
p.Title,
STUFF (
(SELECT ', ' + f.Name
FROM dbo.Facility f
LEFT JOIN dbo.ProjectFacility pf ON f.FacilityId = pf.FacilityId
WHERE pf.ProjectId = p.ProjectId
FOR XML PATH (''))
, 1, 1, '') AS Facilities,
tt.Recieved,
tt.Concluded,
b.BranchName,
st.Description AS StatusText,
tt.Comment,
tt.Concluded - tt.Recieved AS Turnaround
FROM
dbo.Project p
INNER JOIN @TempTable tt ON p.ProjectId = tt.ProjectId
LEFT JOIN dbo.ProjectBranch pb ON p.ProjectId = pb.ProjectId
LEFT JOIN dbo.Branch b ON pb.BranchId = b.BranchId
LEFT JOIN dbo.Status st ON p.StatusId = st.StatusId
WHERE
p.StatusId = 5
AND pb.BranchId = @BranchId
OR p.StatusId = 6
AND pb.BranchId = @BranchId
OR p.StatusId = 7
AND pb.BranchId = @BranchId
SET @Count = @@ROWCOUNT
END
IF @StatusId = 0 AND @BranchId = 0
BEGIN
INSERT INTO @TempTable (ProjectId, Recieved, Concluded, Comment)
SELECT DISTINCT
p.ProjectId,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 1
ORDER BY wf.WorkflowHistoryId DESC) AS Recieved,
(SELECT TOP 1 wf.ActionedOn
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Concluded,
(SELECT TOP 1 wf.Comment
FROM WorkflowHistory wf
WHERE wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 4
OR wf.ProjectId = p.ProjectId
AND wf.WorkflowStep = 5
ORDER BY wf.WorkflowHistoryId DESC) AS Comment
FROM
Project p
JOIN WorkflowHistory w ON p.ProjectId = w.ProjectId
JOIN ProjectBranch pb ON pb.ProjectId = p.ProjectId
WHERE
p.ProjectId = w.ProjectId
AND p.StatusId = 5
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
OR p.ProjectId = w.ProjectId
AND p.StatusId = 6
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
OR p.ProjectId = w.ProjectId
AND p.StatusId = 7
AND w.WorkflowStep = 1
AND (w.ActionedOn BETWEEN @FromDate AND @ToDate)
SELECT DISTINCT
p.ProjectId,
p.Title,
STUFF (
(SELECT ', ' + f.Name
FROM dbo.Facility f
LEFT JOIN dbo.ProjectFacility pf ON f.FacilityId = pf.FacilityId
WHERE pf.ProjectId = p.ProjectId
FOR XML PATH (''))
, 1, 1, '') AS Facilities,
tt.Recieved,
tt.Concluded,
b.BranchName,
st.Description AS StatusText,
tt.Comment,
tt.Concluded - tt.Recieved AS Turnaround
FROM
dbo.Project p
INNER JOIN @TempTable tt ON p.ProjectId = tt.ProjectId
LEFT JOIN dbo.ProjectBranch pb ON p.ProjectId = pb.ProjectId
LEFT JOIN dbo.Branch b ON pb.BranchId = b.BranchId
LEFT JOIN dbo.Status st ON p.StatusId = st.StatusId
WHERE
p.StatusId = 5
OR p.StatusId = 6
OR p.StatusId = 7
SET @Count = @@ROWCOUNT
END
END
回答by TomT
There are few things wrong with your code:
您的代码有几处错误：
- You are repeating the same code over and over again. Instead branching by @StatusId and @BranchId you can handled it in the
WHEREclause. - You are using @StatusId = 0 and @BranchId = 0 for missing value, this should be represented by NULL.
- Where you are using scalar correlated sub-queries I'd use joins.
- 您一遍又一遍地重复相同的代码。您可以在
WHERE子句中处理它，而不是通过 @StatusId 和 @BranchId 进行分支。 - 您正在使用 @StatusId = 0 和 @BranchId = 0 作为缺失值，这应由 NULL 表示。
- 在您使用标量相关子查询的地方，我会使用连接。
Enough talking, I believe this query does what you want:
说得够多了，我相信这个查询可以满足您的需求：
-- find out the final step for each project
WITH FinalStep AS
(
SELECT ProjectId, MAX(WorkflowStep) as MaxWorkflowStep
FROM WorkflowHistory
WHERE WorkflowStep > 1
GROUP BY ProjectId
)
SELECT
p.ProjectId,
p.Title,
-- this is ugly consider creating a scalar function to encapsulate it
STUFF (
(SELECT ', ' + f.Name
FROM dbo.Facility f
LEFT JOIN dbo.ProjectFacility pf ON f.FacilityId = pf.FacilityId
WHERE pf.ProjectId = p.ProjectId
FOR XML PATH (''))
, 1, 1, '') AS Facilities,
wf1.ActionedOn AS Recieved,
wf2.ActionedOn AS Concluded,
b.BranchName,
st.Description AS StatusText,
wf2.Comment,
wf2.ActionedOn - w1.ActionedOn AS Turnaround
FROM
Project p
INNER JOIN WorkflowHistory wf1
ON p.ProjectId = wf1.ProjectId
AND wf1.WorkflowStep = 1
LEFT JOIN FinalStep fs
ON fs.ProjectId = p.ProjectId
LEFT JOIN WorkflowHistory wf2
ON p.ProjectId = wf2.ProjectId
AND wf2.WorkflowStep = fs.MaxWorkflowStep
INNER JOIN ProjectBranch pb
ON pb.ProjectId = p.ProjectId
LEFT JOIN dbo.Branch b
ON pb.BranchId = b.BranchId
LEFT JOIN dbo.Status st
ON p.StatusId = st.StatusId
WHERE
p.ProjectId = w.ProjectId
-- IF @StatusId = 0 THEN p.StatusId = 5 or 6 or 7 ELSE p.StatusId = @StatusId
AND ((@StatusId = 0 AND p.StatusId IN (5,6,7)) OR p.StatusId = @StatusId)
-- IF @BranchId = 0 THEN no filter ELSE pb.BranchId = @BranchId
AND (@BranchId = 0 OR pb.BranchId = @BranchId)
AND (wf1.ActionedOn BETWEEN @FromDate AND @ToDate)
I have no idea why the date filter is not working, looks ok to me. I didn't inspect the facility concatenation either but you should be able to google this stuff pretty easily. I suggest you extract the code into a function. You can then test it separately and the query will look much nicer.
我不知道为什么日期过滤器不起作用，对我来说看起来不错。我也没有检查设施连接，但你应该能够很容易地在谷歌上搜索这些东西。我建议您将代码提取到一个函数中。然后您可以单独测试它，查询会看起来更好。
