首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >IF...ELSE conditional始终返回ELSE条件

问IF...ELSE conditional始终返回ELSE条件
EN

Stack Overflow用户
提问于 2018-08-15 03:32:27
回答 1查看 1.2K关注 0票数 0

我有一个类似下面这样的简单查询:

代码语言:javascript
复制
@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是否为null执行不同的selects,因此我执行查询如下:

代码语言:javascript
复制
exec usp_Get_EmpoyeeByDesign 

如果我将@DesignKey发送为:

代码语言:javascript
复制
exec usp_Get_EmpoyeeByDesign @DesignKey = 2837

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

代码语言:javascript
复制
WHERE [E].[EmpKey] IS NOT NULL
                         AND [T].[DesignKey] = 2837

它抛出了渴望的结果。所以在结论中,查询不会验证@DesignKey是否出现。这里出了什么问题?问候

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-08-15 03:34:14

用IS NOT NULL替换!= NULL。

任何带有NULL的操作都返回NULL,而不是True。

把NULL想象成“我不知道”的意思。

你有两篮苹果。其中一个有7个苹果。另一个篮子被盖住了,所以你不知道里面有多少苹果。这两个篮子里的苹果数量是一样的吗?他们有不同数量的苹果吗?

代码语言:javascript
复制
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

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

票数 6
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51848582

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档