如果 SQL Server CASE 值为 NULL，则不返回任何内容

Question

我有一个函数，它根据某些条件返回多行数据，其中一列。

ALTER FUNCTION [dbo].[GetFavoriteFruits]
(   
    @FruitId uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    Select Name FROM Fruits WHERE FruitID = @FruitId
    UNION ALL
    Select Name FROM Vegetables WHERE VegetableID = @FruitId
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) THEN 'Fruit Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Vegetable Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) OR EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Either Fruit or Vegetable exists'
    END
)

当表中有水果和蔬菜的值时，一切都很好。但是，如果前两种情况的值不存在，则返回的输出(来自最后 3 个查询(为

NULL
NULL
NULL

有没有办法避免这些 NULL 并且不返回与前两个查询类似的任何内容。

Answer 1

我绝不期望这是确认的答案，我只是发布这个是为了向您展示您上面评论的答案：

 ALTER FUNCTION [dbo].[GetFavoriteFruits]
(   
    @FruitId uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT x.Name FROM (
    Select Name FROM Fruits WHERE FruitID = @FruitId
    UNION ALL
    Select Name FROM Vegetables WHERE VegetableID = @FruitId
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) THEN 'Fruit Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Vegetable Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) OR EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Either Fruit or Vegetable exists'
    END
) ) AS x
WHERE x.Name IS NOT NULL

Answer 2

...
SELECT 'Fruit Exists'
WHERE EXISTS(Select 1 FROM Fruits WHERE FruitID = @FruitId)
...

Answer 3

使用 COALESCE 替换 NULL 值。

https://msdn.microsoft.com/en-us/library/ms190349(v=sql.110(.aspx