So I have EXISTS
in huge query which looks like this:
EXISTS( SELECT * FROM ExistTable WHERE ExTableFieldA = @SomeGuid AND ExTableFieldB = MainTableFieldB AND ExTableFieldA <> ( CASE WHEN MainTableFieldZ = 10 THEN MainTableFieldYYY ELSE NULL END ) )
The problem comes from ELSE
part of CASE
statement, this ExTableFieldA <> NULL
will be always false. I could easily write another parameter @EmptyGuid and make it equal to '00000000-0000-0000-0000-000000000000'
and everything will work but is this the best approach ?
Pretty much I want to execute another check into the exist for the small size of the records which return the “main” query.
Advertisement
Answer
How about removing the case
and just using boolean logic?
WHERE ExTableFieldA = @SomeGuid AND ExTableFieldB = MainTableFieldB AND (MainTableFieldZ <> 10 OR ExTableFieldA <> MainTableFieldYYY)
I would also recommend that you qualify the column names by including the table alias.
Note: This does assume that MainTableFieldZ
is not NULL
. If that is a possibility than that logic can easily be incorporated.