CODEWITHSUNDEEP
sql-servert-sql
Rene
Rene

Reputation: 105

IF...ELSE conditional always return ELSE conditional

I have a simple query like this:

@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

As you can see it will execute different selects depending if @DesignKey comes null or not, so I execute query as:

exec usp_Get_EmpoyeeByDesign

and it runs correctly it execute second select now if I send @DesignKey as:

exec usp_Get_EmpoyeeByDesign @DesignKey = 2837

It load same result, it doesn't take care my if conditional I test it sending parameter of designKey in where clause as:

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

and it throws desire results. So in conclusion query don't validate if @DesignKey come or not. What is wrong there? Regards

Upvotes: 0

Views: 1536

Answers (1)

David Dubois
David Dubois

Reputation: 3932

Replace != NULL with IS NOT NULL.

Any operation with NULL returns NULL, not True.

Think of NULL as meaning "I don't know".

You have two baskets of apples. One of them has 7 apples. The other basket is covered so you don't know how many apples there are. Do the two baskets have the same number apples? Do they have a different number of apples?

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

Since you don't know how many apples are in the second bucket you can't know that the number of apples are equal, nor can you know whether they are not equal.

Upvotes: 6

Related Questions