英文:
Re-organizing SQL stored procedure
问题
以下是翻译好的部分：
"我是存储过程的新手。以下存储过程尝试获取会议附件发布者的详细信息。我将meeting_id作为参数传递，并从meetingPublish表中获取发布者用户ID。我想要使用这个信息从EmployeeInfo、Department、Designation和ULC Branch表中检索EmployeeID、UserName、BranchName、DepartmentName、Desugnation等信息。
这个存储过程的问题在于它只返回最后一个发布者的详细信息。我如何改变它，以便可以获取一个MeetingID下的所有发布者详细信息。
USE [Intranet]
GO
/****** 对象： 存储过程 [MeetingManagement].[prGetMeetingMakerDetailsByMeetingId] 脚本日期：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+')' as 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
英文:
I am new to Stored Procedure.
This below stored procedure is trying to get Meeting attachment publisher details.
I am passing meeting_id as parameter and from meetingPublish Table I am getting the Publisher UserId. using that I want to retrive EmployeeID, UserName, BranchName, DepartmentName, Desugnation etc from Table EmployeeInfo,Department, Designation, ULC Branch.
> Problem with this stored procedure is that it only returns the Last Publisher Details. How can I change this in a way that I can get all
> the Publisher Details under a 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
答案1
得分: 0
以下是您要翻译的内容：
尝试这个..
ALTER PROCEDURE [MeetingManagement].[prGetMeetingMakerDetailsByMeetingId]
(
@MeetingId int
)
AS
BEGIN
--DECLARE @UserId NVARCHAR(50);
--SELECT @UserId = m.PublishBy
--FROM MeetingManagement.MeetingPublish m
-- WHERE m.MeetingId = @MeetingId
选择 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
内连接 SharedData.Department d on e.DepartmentID = d.DepartmentID
内连接 SharedData.Designation g on e.DesignationID = g.DesignationID
内连接 SharedData.ULCBranch b on e.ULCBranchID = b.ULCBranchID
左连接 [SharedData].EmployeeInfo se ON se.EmployeeID = e.CurrentSupervisor
其中 e.isActive=1
--and e.UserID = @UserId
AND 存在 ( SELECT 1
FROM MeetingManagement.MeetingPublish m
WHERE m.MeetingId = @MeetingId
AND e.UserID = m.PublishBy
)
并且 e.EmployeeName 不像 '%N\A%'
并且 e.EmployeeName 不像 '%Ex-%'
并且 e.EmployeeName 不为 null
并且 e.EmpCode 不为 null
按 EmployeeName 升序排序
END
英文:
Try this..
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 EXISTS ( SELECT 1
FROM MeetingManagement.MeetingPublish m
WHERE m.MeetingId = @MeetingId
AND e.UserID = m.PublishBy
)
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论