Conditional WHERE clause in SQL Server

Going conditional: Multi-conditions handling

Beware the bulky scripts—they're bad news. Keep it simple, keep it clean, handle multiple conditions without creating a Lernaean Hydra (yes those queries that keep spawning):

/* Hydra-slaying WHERE clause */
SELECT * FROM Jobs
WHERE (@JobsOnHold IS NULL OR JobsOnHold = @JobsOnHold)
AND (DateAppr >= ISNULL(@DateAppr, DateAppr))


Filter-friendly: @JobsOnHold with a value filters, does a no-show, voila - no Hydra!

Keep it dynamic: ISNULL for logical simplicity

ISNULL function can replace dynamic SQL:

/* Dynamic Bruce Lee move, no needles extra */
SELECT * FROM Bookings
WHERE ISNULL(DateAppr, 1) = CASE WHEN @JobsOnHold = 1 THEN ISNULL(DateAppr, 1) ELSE 0 END


Filters DateAppr only when @JobsOnHold is not 1—like a bouncer deciding who gets in the club.

Performance tuning with advanced SQL tricks

Trading CASE for Boolean: Gear up your performance

Swap CASE expressions with sleek boolean comparisons for performance boosts:

/* SQL in overdrive */
SELECT * FROM Members
WHERE (@JobsOnHold = 1 AND DateAppr >= 0) OR
      (@JobsOnHold != 1 AND DateAppr != 0)


Welcome to the fast lane—SQL queries optimized for large datasets and indexes.

SQL performance tricks: OPTION (RECOMPILE)

OPTION (RECOMPILE) preps SQL to optimize the execution plan based on variable values:

/* Knight Rider Turbo Boost activated! */
SELECT * FROM Orders
WHERE Status = CASE WHEN @FilterOption = 'Open' THEN 'Open' ELSE Status END
OPTION (RECOMPILE)


And just like that, SQL recalibrates to the current @FilterOption value.

Balance act: Maintainability vs Performance

Should I write clean code? Should I optimize for performance? Why not both:

/* SQL's grace of a gazelle and might of a lion...in balance */
SELECT * FROM Transactions
WHERE (Status = 'Pending' OR @ShowAll = 1) AND
      (Type = @TransactionType OR @TransactionType IS NULL)


Readability and performance co-existing peacefully, as nature intended.