Switch between Select All and Where clause dynamically in SQL Server [duplicate]

Question

I want to write a T-SQL query for below conditions.

If the user provides a value for the parameter, run the query with the where clause.

SELECT * FROM TestDB WHERE CustomerId = @customer_id

If the user doesn't provide a value for the parameter, I want to return all the data

SELECT * FROM TestDB

Can I make it work in a single query, for example using ISNULL() or an alternative, without going for a stored procedure.

Tim Biegeleisen · Accepted Answer · 2021-09-07 11:22:29Z

2

Add a condition to the WHERE clause which admits all records in the event the user parameter be null:

SELECT * FROM TestDB WHERE CustomerId = @customer_id OR @customer_id IS NULL;

answered Sep 7, 2021 at 11:22

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I suggest one specify an OPTION(RECOMPILE) query hint with this technique in order to optimize the query for either use case.