Dyanmic Where Clause-CodePudding

Is there a way to make your comparison operator dynamic?

EG:

SELECT
    *

FROM TABLE

WHERE
    IIF(@FLAG_A = 'True', COLUMN_A = @COLUMN_A, COLUMN_A <> @COLUMN_A)
AND IIF(@FLAG_B = 'True', COLUMN_B = @COLUMN_B, COLUMN_B <> @COLUMN_B)
AND IIF(@FLAG_C = 'True', COLUMN_C = @COLUMN_C, COLUMN_C <> @COLUMN_C)

I tried the above example with my parameters set as @FLAG_A = 'True', @FLAG_B = 'False' @FLAG_C = 'False'

I was expecting this query to run:

SELECT
    *

FROM TABLE

WHERE
    COLUMN_A = @COLUMN_A
AND COLUMN_B <> @COLUMN_B
AND COLUMN_C <> @COLUMN_C

But I'm getting: Incorrect syntax near '='.

CodePudding user response：

You need to use explicit AND and OR logic. Also adding RECOMPILE to the OPTION clause is going to be a likely benefit, as the plans could be very different for each set of variables.

Unfortunately, due to your logic, this does look someone messy:

SELECT {Column List} --Don't use SELECT *
FROM dbo.YourTable
WHERE ((@FLAG_A = 'true' AND COLUMN_A = @COLUMN_A) OR (@FLAG_A <> 'true' AND COLUMN_A <> @COLUMN_A))
  AND ((@FLAG_B = 'true' AND COLUMN_B = @COLUMN_B) OR (@FLAG_B <> 'true' AND COLUMN_B <> @COLUMN_B))
  AND ((@FLAG_C = 'true' AND COLUMN_C = @COLUMN_C) OR (@FLAG_C <> 'true' AND COLUMN_C <> @COLUMN_C))
OPTION (RECOMPILE);

The alternative approach would be to create a parametrised dynamic statement. This may perform better (and allows for caching of plans), however, does require a better level of understanding:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SET @SQL = N'SELECT {Column List}'   @CRLF  
           N'FROM dbo.YourTable'   @CRLF  
           N'WHERE '   IIF(@FLAG_A = 'True',N' COLUMN_A = @COLUMN_A', N'COLUMN_A <> @COLUMN_A')   @CRLF  
           N'  AND '   IIF(@FLAG_B = 'True',N' COLUMN_B = @COLUMN_B', N'COLUMN_B <> @COLUMN_B')   @CRLF  
           N'  AND '   IIF(@FLAG_C = 'True',N' COLUMN_C = @COLUMN_C', N'COLUMN_C <> @COLUMN_C')   N';';

--Note the data type are guessed, and you will need to correct these.
EXEC sys.sp_executesql @SQL, N'@COLUMN_A varchar(20), @COLUMN_B varchar(20), @COLUMN_C varchar(20)', @COLUMN_A, @COLUMN_B, @COLUMN_C;