SQL add filter only if a variable is not null

by

You can translate your requirement into :

SELECT  route_id [ROUTE_ID]
FROM route_master(NOLOCK)
WHERE  route_ou = 2
AND   (@l_s_query is null OR route_query = @l_s_query)
AND   lang_id  = 1
OPTION (RECOMPILE)

The OPTION (RECOMPILE) is optional but can give better execution plans at the expense of extra compilation time as discussed in the canonical article on the topic Dynamic Search Conditions in T‑SQL

Or with COALESCE() to avoid the OR :

WHERE  route_ou = 2
AND   COALESCE(@l_s_query,route_query) = route_query 
AND   lang_id  = 1

Note: As @jarlh said, if route_query is nullable, this may cause some issues becuase of null comparison, so you may want to use the first query.

Another option of this is two separate queries using UNION ALL , one for each condition –

SELECT .. FROM .. 
WHERE @l_s_query IS NULL
UNION ALL
SELECT .. FROM .. 
WHERE @l_s_query = route_query

On terms of performance,only the last one will use the index, I believe the first one will be the fastest, but it may change depanding on the indexes, sizes of the tables ETC..

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. When should I use CROSS APPLY over INNER JOIN?
  3. SQL Server – Return value after INSERT
  4. SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, =
  5. Parameterize an SQL IN clause
  6. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  7. Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
  8. When no ‘Order by’ is specified, what order does a query choose for your record set?
  9. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  10. Recursive query in SQL Server
  11. SQL Call Stored Procedure for each Row without using a cursor
  12. How to select the last record of a table in SQL?
  13. How do I create a foreign key in SQL Server?
  14. How to create table using select query in SQL Server?
  15. How do I group on continuous ranges
  16. join comma delimited data column
  17. conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  18. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  19. How do I pass a list as a parameter in a stored procedure?
  20. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  21. Get previous and next row from rows selected with (WHERE) conditions
  22. Can you have if-then-else logic in SQL? [duplicate]
  23. How to list files inside a folder with SQL Server
  24. Can SQL Server Express LocalDB be connected to remotely?
  25. SQL – Call Stored Procedure for each record
  26. SQL – How do I get only the numbers after the decimal?
  27. How to retrieve records for last 30 minutes in MS SQL?
  28. Connecting to Oracle Database using Sql Server Integration Services
  29. Why do SQL Server Scalar-valued functions get slower?
  30. Executing SQL query on multiple databases

Leave a Comment