sql-server - 查询中的SQL查询

Question

我的数据库中有这种结构。我需要建立一个雇员姓名列表，然后是其部门和办公室的名称。现在，我来到了这个查询：

ALTER PROCEDURE FilterEmpList
    @empName nvarchar(250) = null,
    @empDepID int = null,
    @empOfficeID int = null,
    @empPosID int = null    
AS 
BEGIN
SELECT 
    E.emp_id AS 'emp_id',
    E.emp_name AS 'emp_name',      
    (SELECT pos_name FROM Positions WHERE pos_id=EP.pos_id) AS 'pos_name',
    (SELECT dep_name FROM Departments WHERE dep_id=OS.dep_id) AS 'dep_name',
    (SELECT office_name FROM Offices WHERE office_id=OS.office_id) AS 'office_name'
FROM 
    Org_Structure OS INNER JOIN (Emp_Positions EP INNER JOIN Employees E ON EP.emp_id=E.emp_id) ON OS.chain_id=EP.chain_id      
WHERE 
    (E.emp_name LIKE '%'+@empName+'%' OR @empName IS NULL)
    AND
    (OS.dep_id = @empDepID OR @empDepID IS NULL)
    AND
    (OS.office_id = @empOfficeID OR @empOfficeID IS NULL)
    AND
    (EP.pos_id = @empPosID OR @empPosID IS NULL)
END

当我以这种方式执行exec FilterEmpList时，会发生错误：无效的对象名称'EP'。

对我来说很难，这个查询是我的最大任务，花了我整天的时间。

下期

现在，此查询还有一个问题。如果Org_Structure中的office_id或dep_id为NULL，则根本不返回该记录。 WHERE子句出了点问题。

使用WHERE子句解决此问题的方法是我的另一个主题： WHERE clause - record with a NULL column is not displayed

谢谢大家。问题已解决

Answer 1

可能对您有帮助-

ALTER PROCEDURE dbo.FilterEmpList
    @empName nvarchar(250) = null,
    @empDepID int = null,
    @empOfficeID int = null,
    @empPosID int = null    
AS 
BEGIN

    SELECT 
        E.emp_id,
        E.emp_name,      
        p.pos_name,
        d.dep_name,
        o.office_name
    FROM dbo.Org_Structure OS 
    JOIN dbo.Emp_Positions EP ON OS.chain_id=EP.chain_id  
    /*LEFT*/ JOIN dbo.Employees E ON EP.emp_id=E.emp_id 
    /*LEFT*/ JOIN dbo.Positions p ON p.pos_id=EP.pos_id
    /*LEFT*/ JOIN dbo.Departments d ON d.dep_id=OS.dep_id
    JOIN dbo.Offices o ON o.office_id=OS.office_id  
    WHERE (
              E.emp_name LIKE '%'+@empName+'%' 
            OR 
              @empName IS NULL
        )
        AND OS.dep_id = ISNULL(@empDepID, OS.dep_id)
        AND OS.office_id = ISNULL(@empOfficeID, OS.office_id)
        AND EP.pos_id = ISNULL(@empPosID, EP.pos_id)

END