时间戳

首页 > 解决方案 > IF...ELSE 条件总是返回 ELSE 条件

sql-server - IF...ELSE 条件总是返回 ELSE 条件

问题描述

我有一个这样的简单查询：

@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 是否出现。那里有什么问题？问候

标签： sql-servertsql

解决方案

替换 != 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

因为你不知道第二个桶里有多少个苹果，所以你不知道苹果的数量是否相等，也不知道它们是否不相等。

推荐阅读