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.
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.