CODEWITHSUNDEEP
sqlsql-servert-sql
Pepe
Pepe

Reputation: 125

Where clause returns different result with same logic

I'm new in sql and I have where clause like this:

First Query (production)

WHERE [D].[IsLocked] = 0
                        AND ((@FilterEmpKey IS NULL
                        AND [TA].[EmpKey] = @CurrentEmpKey)
                         OR (ISNULL([TA].[ModifiedAssignedBy] , [TA].[AssignatedBy]) = @FilterEmpKey
                        AND [TA].[EmpKey] = @CurrentEmpKey))

Second Query(test)

WHERE [D].[IsLocked] = 0 
              AND  [TA].EmpKey = @CurrentEmpKey  
              OR (ISNULL([TA].[ModifiedAssignedBy] , [TA].[AssignatedBy]) = @FilterEmpKey
              AND [TA].[EmpKey] = @CurrentEmpKey)

I want to know what is the difference between first and second query, because result is not the same and I don't know why?. Regards

Note: my desire result is get value of test query

Upvotes: 0

Views: 140

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

Here's an example. The queries return different result sets when @FilterEmpKey IS NOT NULL and @CurrentEmpKey matches TA.EmpKey.

CREATE TABLE TA (EmpKey VARCHAR(25), ModifiedAssignedBy VARCHAR(25), AssignatedBy VARCHAR(25))
INSERT INTO TA VALUES ('test', 'test_mod', 'test_assign')

DECLARE @FilterEmpKey VARCHAR(25) = 'banana'
DECLARE @CurrentEmpKey VARCHAR(25) = 'test'

-- Returns nothing; both conditions are false.
SELECT * FROM TA
WHERE  ((@FilterEmpKey IS NULL
                        AND [TA].[EmpKey] = @CurrentEmpKey)
                         OR (ISNULL([TA].[ModifiedAssignedBy] , [TA].[AssignatedBy]) = @FilterEmpKey
                        AND [TA].[EmpKey] = @CurrentEmpKey))

-- Returns a row; first condition is true.
SELECT * FROM TA
WHERE [TA].EmpKey = @CurrentEmpKey  
              OR (ISNULL([TA].[ModifiedAssignedBy] , [TA].[AssignatedBy]) = @FilterEmpKey
              AND [TA].[EmpKey] = @CurrentEmpKey)

Upvotes: 1

Related Questions