如何修改SQL存储过程以获取指定MeetingID下所有发布者详情

我是存储过程新手，现有存储过程用于获取会议附件发布者详情：传入meeting_id参数，从MeetingPublish表获取发布者UserId，再通过该UserId关联EmployeeInfo、Department、Designation、ULCBranch表获取EmployeeID、UserName、BranchName等信息。但当前存储过程仅返回最后一位发布者的详情，请问如何修改才能获取指定MeetingID下的所有发布者详情？

原存储过程代码 #

USE [Intranet]
GO
/****** Object:  StoredProcedure [MeetingManagement].[prGetMeetingMakerDetailsByMeetingId]    Script Date: 5/25/2023 5:08:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MeetingManagement].[prGetMeetingMakerDetailsByMeetingId] 
(
    @MeetingId int
)
AS
BEGIN
DECLARE @UserId NVARCHAR(50);


    SELECT @UserId = m.PublishBy 
    FROM MeetingManagement.MeetingPublish m
     WHERE m.MeetingId = @MeetingId

    Select E.UserID AS UserId , e.EmployeeName +'('+e.EmpCode+')' as EmployeeName,e.EmployeeID,
    g.DesignationName,d.DeptName,b.ULCBranchName,e.MobileNo,e.Photos,e.MailAddress,
    se.EmployeeName+'('+se.EmpCode+')' CurrentSupervisor,
    se.MailAddress as CurrentSupervisormail,
    se.UserID as SupervisorId
    
    from [SharedData].EmployeeInfo e
    INNER JOIN SharedData.Department d on e.DepartmentID = d.DepartmentID
    INNER JOIN SharedData.Designation g on e.DesignationID = g.DesignationID
    inner join SharedData.ULCBranch b on e.ULCBranchID = b.ULCBranchID
    LEFT JOIN [SharedData].EmployeeInfo se ON se.EmployeeID = e.CurrentSupervisor
    where e.isActive=1
    and e.UserID = @UserId
    and e.EmployeeName not like '%N\A%'
    and e.EmployeeName not like '%Ex-%'
    and e.EmployeeName is not null
    and e.EmpCode is not null 
    ORDER by EmployeeName asc
END

问题原因 #

原代码中用变量@UserId接收PublishBy字段值，当MeetingPublish表中同一个MeetingId对应多条发布记录时，变量只会存储最后一条记录的UserId，因此最终查询只能返回单个用户的信息。

修改后的存储过程代码 #

直接将MeetingPublish表与员工相关表关联，一次性查询所有符合条件的发布者信息：

USE [Intranet]
GO
/****** Object:  StoredProcedure [MeetingManagement].[prGetMeetingMakerDetailsByMeetingId]    Script Date: 5/25/2023 5:08:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [MeetingManagement].[prGetMeetingMakerDetailsByMeetingId] 
(
    @MeetingId int
)
AS
BEGIN
    SELECT 
        e.UserID AS UserId,
        e.EmployeeName + '(' + e.EmpCode + ')' AS EmployeeName,
        e.EmployeeID,
        g.DesignationName,
        d.DeptName,
        b.ULCBranchName,
        e.MobileNo,
        e.Photos,
        e.MailAddress,
        se.EmployeeName + '(' + se.EmpCode + ')' AS CurrentSupervisor,
        se.MailAddress AS CurrentSupervisormail,
        se.UserID AS SupervisorId
    FROM 
        MeetingManagement.MeetingPublish m
    INNER JOIN 
        [SharedData].EmployeeInfo e ON m.PublishBy = e.UserID
    INNER JOIN 
        SharedData.Department d ON e.DepartmentID = d.DepartmentID
    INNER JOIN 
        SharedData.Designation g ON e.DesignationID = g.DesignationID
    INNER JOIN 
        SharedData.ULCBranch b ON e.ULCBranchID = b.ULCBranchID
    LEFT JOIN 
        [SharedData].EmployeeInfo se ON se.EmployeeID = e.CurrentSupervisor
    WHERE 
        m.MeetingId = @MeetingId
        AND e.isActive = 1
        AND e.EmployeeName NOT LIKE '%N\A%'
        AND e.EmployeeName NOT LIKE '%Ex-%'
        AND e.EmployeeName IS NOT NULL
        AND e.EmpCode IS NOT NULL 
    ORDER BY 
        e.EmployeeName ASC
END

修改说明 #

1. 移除了@UserId变量，直接通过INNER JOIN将MeetingPublish表与EmployeeInfo表关联，关联条件为m.PublishBy = e.UserID
2. 将原查询中e.UserID = @UserId的条件替换为m.MeetingId = @MeetingId，确保只查询指定会议的发布者
3. 保持原有的员工信息过滤条件和排序逻辑不变，确保返回数据的有效性和顺序

内容的提问来源于stack exchange，提问作者Mahir Tazwar