如果..ELSE 条件总是返回 ELSE 条件

Question

我有一个简单的查询，如下所示：

@DesignKey [INT] = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF(@DesignKey != NULL)
BEGIN
SELECT
[P].[LegacyKey] AS [Job No]
, [TT].[Name] AS [Task]
, CONCAT([E].[FirstName] , ' ' , [E].[MaidenName]) AS [Technician]
FROM [Task] AS [T]
LEFT JOIN [TaskAssignation] AS [TA] ON [T].[TaskAssignationId] = [TA].[TaskAssignationId]
LEFT JOIN [Employee] AS [E] ON [TA].[EmpKey] = [E].[EmpKey]
INNER JOIN [TaskType] AS [TT] ON [T].[TaskTypeId] = [TT].[TaskTypeId]
INNER JOIN [Design] AS [D] ON [T].[DesignKey] = [D].[DesignKey]
INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
WHERE [E].[EmpKey] IS NOT NULL
AND [T].[DesignKey] = @DesignKey
END
ELSE
BEGIN
SELECT
[P].[LegacyKey] AS [Job No]
, [TT].[Name] AS [Task]
, CONCAT([E].[FirstName] , ' ' , [E].[MaidenName]) AS [Technician]
FROM [Task] AS [T]
LEFT JOIN [TaskAssignation] AS [TA] ON [T].[TaskAssignationId] = [TA].[TaskAssignationId]
LEFT JOIN [Employee] AS [E] ON [TA].[EmpKey] = [E].[EmpKey]
INNER JOIN [TaskType] AS [TT] ON [T].[TaskTypeId] = [TT].[TaskTypeId]
INNER JOIN [Design] AS [D] ON [T].[DesignKey] = [D].[DesignKey]
INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]
INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]
WHERE [E].[EmpKey] IS NOT NULL
END
END

如您所见，它将根据@DesignKey是否为空执行不同的选择，因此我执行查询为：

exec usp_Get_EmpoyeeByDesign 

并且它运行正常，如果我以以下方式发送@DesignKey，它现在执行第二个选择：

exec usp_Get_EmpoyeeByDesign @DesignKey = 2837

它加载相同的结果，它不关心我的if条件我测试它在 where 子句中发送 designKey 的参数为：

WHERE [E].[EmpKey] IS NOT NULL
AND [T].[DesignKey] = 2837

它抛出欲望的结果。因此，在结论中，查询不会验证@DesignKey是否来。那里有什么问题？问候

Answer 1

将!= NULL替换为IS NOT NULL。

任何具有 NULL 的操作都返回 NULL，而不是 True。

将 NULL 视为"我不知道"的意思。

你有两篮苹果。 其中一个有7个苹果。 另一个篮子被盖住了，所以你不知道有多少个苹果。两个篮子的苹果数量相同吗？ 他们有不同数量的苹果吗？

declare @a int, @b int
set @a = 7    -- 7 apples
set @b = NULL -- I don't know how many apples
if @a = @b 
print 'Equal' -- doesn't print
if @a <> @b 
print 'Not equal' -- doesn't print

由于你不知道第二个桶中有多少个苹果，你不可能知道苹果的数量是相等的，你也不能知道它们是否相等。