SQL Server: Using CASE WHEN expression with WHERE clause

Question

I have a new Spring Boot project with SQL Server and I need to replace the MySQL native queries on the repository methods in the old project with SQL Server native queries. This is a complex query where the condition is a case when expression. When I try to test the query in SQL Server Management Studio, it displays an 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 SQL Server Enter image description here Please help me find a solution. Thank you in advance! !

Answer 1

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.