如果存储过程中的语句(总菜单)

背景：我有一个有人列表的表，还有另一个用于发生的事件的表。我需要一个日期，即事件发生在一个人的列中更新的人。

的数据库中。

所以我的理解是，我需要使用SQL Server代理创建并运行一个为我做到这一点的存储过程。我从来没有写过存储程序，所以这也是我的实践。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Gary Senter
-- Create date: 5/25/2019
-- Description: Returns employee data.
-- =============================================
ALTER PROCEDURE [dbo].[spLocal_Safety_Sync] 
    @LastName NVARCHAR(50) = NULL, 
    @FirstName NVARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @injury_date DATETIME;
    DECLARE @short_name_1 VARCHAR(50);
    DECLARE @short_name_2 VARCHAR(50);
    DECLARE @short_name_3 VARCHAR(50);
    DECLARE @cc_roster_id_emp_1 INT;
    DECLARE @cc_roster_id_emp_2 INT;
    DECLARE @cc_roster_id_emp_3 INT;
    SELECT 
        @short_name_1 = effected_employee_1,
        @short_name_2 =  effected_employee_2, 
        @short_name_3 = effected_employee_3,
        @injury_date = IncidentDate 
    FROM
        HSEBE.dbo.MCA_Data
    WHERE  
        (Classification = 'XX RI' OR Classification = 'YY RI')
    ORDER BY 
        IncidentDate ASC
    IF LEN(@short_name_1) > 0 
    THEN
        SELECT @cc_roster_id_emp_1 = autoid 
        FROM cc_rosters 
        WHERE short_name = @short_name_1
    IF LEN(@short_name_2) > 0 
    THEN
        SELECT @cc_roster_id_emp_2 = autoid 
        FROM cc_rosters 
        WHERE short_name = @short_name_2
    IF LEN(@short_name_3) > 0 
    THEN
        SELECT @cc_roster_id_emp_3 = autoid 
        FROM cc_rosters 
        WHERE short_name = @short_name_3
    IF @cc_roster_id_emp_1 > 0 
    THEN
        UPDATE cc_rosters 
        SET most_recent_injury = @injury_date 
        WHERE autoid = @short_name_1
    IF @cc_roster_id_emp_2 > 0 
    THEN
        UPDATE cc_rosters 
        SET most_recent_injury = @injury_date 
        WHERE autoid = @short_name_2
    IF @cc_roster_id_emp_3 > 0 
    THEN
        UPDATE cc_rosters 
        SET most_recent_injury = @injury_date 
        WHERE autoid = @short_name_3
END

我得到这些错误：

msg 156，15级，状态1，过程splocal_safety_sync，第28行
关键字附近的语法不正确。
msg 156，15级，状态1，过程splocal_safety_sync，第30行
关键字附近的语法不正确。
msg 156，15级，状态1，过程splocal_safety_sync，第32行
关键字附近的语法不正确。
msg 156，15级，状态1，过程splocal_safety_sync，第35行
关键字附近的语法不正确。
msg 156，15级，状态1，过程splocal_safety_sync，第37行
关键字附近的语法不正确。
msg 156，15级，状态1，过程splocal_safety_sync，第39行
关键字附近的语法不正确。

不确定从这里转到哪里....

不会总是有effed_employee_1,2,3，有时选择的值不会从其他数据库中返回。

好的，所以这有点多，我了解您仍在学习，所以让我向您展示如何处理。另外，用一粒盐b/c对此进行此操作，我对数据或预期输出不可能。我想这将使您到达那里。不过是每次都会更新每个员工。您可以更改以检查比赛并轻松排除这些比赛，但这比其他任何事情都更像是一个教学时刻。

首先让我解释一下我在这里看到的问题：

看起来您在MCA_DATA表中的数据结构不好。我的意思是它是一张"平坦"桌子，一排有多个员工进行事件。通常，这是一个PITA，当时它可以在许多情况下完成任务以查看数据。通常会有一张表格，事件记录将为每个涉及事件ID和员工ID的人分开记录。我了解您可能没有构建它，但是值得指出的是您自己的信息。

从根本上讲，一旦您理解了小于理想的结构如何影响您的任务是什么，那么此问题就更容易受到帮助。在我看来，这本质上是一个关键问题。SQL中的枢轴基本上是切换行和列。如果我们想为每个员工找到最大事件日期，我们只需要更改MCA_DATA表中的数据即可更容易处理，对吗？有可能可以通过代码向数据转移数据的方法 - 选择您的喜爱 - 大多数人实际使用该功能枢轴，在此我倾向于在CTE(常见的表实验(和枢轴之间摇摆。一旦您理解它们，IMO CTE就更容易阅读。运行此代码，看看是否返回正确的结果。

   ; WITH CTE AS
   (
        SELECT Employee, AutoID, MAX(IncidentDate) AS MaxIncidentDate
        FROM
            (
                SELECT 
                    effected_employee_1 AS Employee,
                    cc1.autoID AS AutoID,
                    MAX(IncidentDate) AS IncidentDate 
                FROM 
                    HSEBE.dbo.MCA_Data mca
                LEFT JOIN 
                    cc_rosters cc1 ON mac. effected_employee_1 = cc1. short_name
                WHERE  
                    (Classification = 'XX RI' OR Classification = 'YY RI')
                    AND effected_employee_1 IS NOT NULL
                GROUP BY 
                    effected_employee_1,
                    cc1.autoID
                UNION
                SELECT 
                    effected_employee_2 AS Employee,
                    cc2.AutoID  AS AutoID,
                    MAX(IncidentDate) AS IncidentDate 
                FROM 
                    HSEBE.dbo.MCA_Data mca
                LEFT JOIN 
                    cc_rosters cc2 ON mac. effected_employee_2 = cc2. short_name
                WHERE  
                    (Classification = 'XX RI' OR Classification = 'YY RI')
                    AND effected_employee_2 IS NOT NULL 
                GROUP BY 
                    effected_employee_2,
                    cc2.autoID                          
                UNION
                SELECT 
                    effected_employee_3 AS Employee, 
                    cc3.autoID AS AutoID,
                    MAX(IncidentDate) AS IncidentDate 
                FROM 
                    HSEBE.dbo.MCA_Data mca
                LEFT JOIN 
                    cc_rosters cc3 ON mac. effected_employee_3 = cc3. short_name
                WHERE  
                    (Classification = 'XX RI' OR Classification = 'YY RI')
                    AND effected_employee_3 IS NOT NULL
                GROUP BY 
                    effected_employee_3,
                    cc3.autoID      
            ) x
        GROUP BY 
            Employee, AutoID,
    )   
SELECT * FROM CTE

现在，在为每个员工提供最大事件日期之后，该列的最大事件日期不是无效的，您只需从这里更新即可。通过连接更新是您想要擅长的东西。防止所有这些IF和变量以及各种陈述。这也可以帮助您以基于设定的方式做事。在继续学习路径时，您将继续听到这一点。我建议理解它！

; WITH CTE AS
(
    SELECT Employee, AutoID, MAX(IncidentDate) AS MaxIncidentDate
    FROM
        (
            SELECT 
                effected_employee_1 AS Employee,
                cc1.autoID AS AutoID,
                MAX(IncidentDate) AS IncidentDate 
            FROM 
                HSEBE.dbo.MCA_Data mca
            LEFT JOIN 
                cc_rosters cc1 ON mac. effected_employee_1 = cc1. short_name
            WHERE  
                (Classification = 'XX RI' OR Classification = 'YY RI')
                AND effected_employee_1 IS NOT NULL
            GROUP BY 
                effected_employee_1,
                cc1.autoID
            UNION
            SELECT 
                effected_employee_2 AS Employee,
                cc2.AutoID  AS AutoID,
                MAX(IncidentDate) AS IncidentDate 
            FROM 
                HSEBE.dbo.MCA_Data mca
            LEFT JOIN 
                cc_rosters cc2 ON mac. effected_employee_2 = cc2. short_name
            WHERE  
                (Classification = 'XX RI' OR Classification = 'YY RI')
                AND effected_employee_2 IS NOT NULL 
            GROUP BY 
                effected_employee_2,
                cc2.autoID                          
            UNION
            SELECT 
                effected_employee_3 AS Employee, 
                cc3.autoID AS AutoID,
                MAX(IncidentDate) AS IncidentDate 
            FROM 
                HSEBE.dbo.MCA_Data mca
            LEFT JOIN 
                cc_rosters cc3 ON mac. effected_employee_3 = cc3. short_name
            WHERE  
                (Classification = 'XX RI' OR Classification = 'YY RI')
                AND effected_employee_3 IS NOT NULL
            GROUP BY 
                effected_employee_3,
                cc3.autoID      
        ) x
    GROUP BY 
        Employee, AutoID,
)
UPDATE r SET r.most_recent_injury = cte.MaxIncidentDate
FROM cc_rosters r
INNER JOIN CTE ON r.AutoID = CTE.AutoID AND r.short_name =cte.Employee;

在您有多个命令的过程中，习惯了使用';'终止每行的字符。这是一个很好的做法。

相关内容

最新更新

热门标签：