mysql - 存储过程的空值

我正在分配作业，此存储过程的基本功能是让用户输入城市名称，并返回该特定城市的员工和客户的名称。我遇到麻烦的地方是，当我运行该程序时，它提供的是重复的名称，而不是任一列中的空值。我也肯定有一个更好的方法将这两个表连接在一起，而不是一个长连接字符串，但是我在画一个空白。

Create Proc DPCityContacts
 @City Varchar(20)
as
Begin

Select Distinct FirstName+', '+LastName as Employee, CustFirstName+', '+CustLastName as      Customer
into #tmp
from Employee E Join Packingslip PS on E.EmployeeID = PS.EmployeeID
Join ShippedItem SI on SI.PackageNumber = PS.PackageNumber Join CustOrder
CO on CO.OrderID = SI.OrderID Join Customer C on C.CustomerID = CO.CustomerID
Where @City = C.City  and @City = E.City and ReleaseDate is null
Group by FirstName

if exists (select 1 from #tmp)
begin
select *
from #tmp;
end
else
Print '“No Employees or Customers in the city of '+ @City
end

最佳答案

我认为这就是您想要的，但是我不确定，因为我无法测试。
首先，我不想所有这些联接，因为City与员工和客户位于同一表中。
而且，在所有情况下，您都希望在客户选择和员工选择之间建立一个UNION。您不能将这两个表连接在一起，否则会得到奇怪的结果。

Select CONCAT(FirstName, ', ', LastName) AS Name, 'EMPLOYEE' AS type
INTO #tmp
FROM Employee
WHERE @City = City and ReleaseDate is null
UNION
SELECT  CONCAT(CustFirstName, ', ', CustLastName) AS Name, 'CUSTOMER' AS type FROM
Customer
INTO #tmp
WHERE @City = City and ReleaseDate is null