SQL Server: Using CASE WHEN expression with WHERE clause

Question

I have a new SpringBoot project with SQLServer and I need to replace the MySQL native queries on the repository methods in the old project with SQLServer native queries. This is a complex query where the condition is a casewhen expression. When I try to test the query in SQLServerManagementStudio, it shows the error as shown in the image below. Enter image description here This is my old native query using MySQL on the repository method and I want to replace it with SQLServer Enter image here

P粉904191507 · Answer

This is what you have and what you should post as text in your question. As text, it can be searched and copied by someone trying to help you.

case when @num = 1 then p.merchant_name = @query else 1=1 end

CASE is an expression in TSQL. It is not a flow control construct like many other languages. To use the "optional" filter, you need to use CASE to construct a boolean expression that correctly handles the "optional" attribute. Typically, this can be done more sophisticatedly using CASE, as follows:

case when @num = 1 and p.merchant_name  @query then 0 else 1 end = 1

So CASE is used here to return a value that can be tested in the comparison. There's nothing magical about using 0 or 1. Use any value of any type.

When @num is 1 and the values do not match, return the THEN branch (0).
When @num is 1 and the values match, return the ELSE branch (1).
When @num is not 1, return the ELSE branch (1).

So when the CASE expression returns 0 (which is not actually 1), the row is ignored (removed from the result set).

Given that your query is actually constructed within the application, you should consider building the query dynamically and adding parameters as needed. This may result in a more efficient query that the database engine can better optimize. Alternatively, you can check out this Kitchen Sink Discussion and Erland's discussion on Dynamic Search Conditions. TBH looks like someone used @num as a patchwork to avoid adding parameters for eight specific filter values. If I want to filter both merchant name and store name, I can't use this method.