Conditional where clause T-SQL

Question

I have a stored procedure that returns data.

I need to change the where clause based on parameters passed in.

For example, the parameters are:

@Region NVARHCAR(15)
@CountryCode NVARCHAR(2)
@ProductA BIT
@ProductB BIT
@ProductC BIT

If @Region is passed in, then the where should select by region, if @CountryCode is passed in then the where should select by country code.

For the products, if any of them are set to true, the where should select the data for that project.

So the statement could look like this if @Region is passed in and @ProductA and @ProductC are set to true:

SELECT *
FROM table
WHERE Region = @Region AND
(Product = 'ProductA' OR Product = 'ProductC')

Alternatively, the product conditional could be an IN statement.

If @CountryCode was passed in it would look as follows:

SELECT *
FROM table
WHERE CountryCode = @CountryCode AND
(Product = 'ProductA' OR Product = 'ProductC')

It's even possible that @CountryCode and @Region could be passed in.

Is there any way to do this with T-SQL and not dynamic SQL generated from the app?

Thanks

Answer 1

You don't need to build a dynamic SQL statement, you just need to check the values of your parameters. Here is how I commonly build SQL clauses to achieve this:

WHERE ((@Region IS NULL) OR (Region = @Region))
AND ((@CountryCode IS NULL) OR (CountryCode = @CountryCode))
AND ((@ProductA = 0) OR (Product = 'ProductA'))
AND ((@ProductB = 0) OR (Product = 'ProductB'))
AND ((@ProductC = 0) OR (Product = 'ProductC'))

If your SQL is built like this, then you are only filtering on the Region column when you pass in a value for the @Region parameter. The same is true for CountryCode.

Answer 2

You can always build the SQL statement as a string using your conditions.. then simply execute the resulting statement string using sp_executesql (a command which basically executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically)...

I understand you may not want build sql strings but it a solution.