There is a certain query with parameters, but not all parameters are always present and the result of the query is an empty table, since the parameter is equal to an empty value and there are no records with an empty value in the table. How to make that if there is no parameter, the request would be executed without it.

    1 answer 1

    SELECT * FROM [Table] WHERE (([Field1] = @param1) OR @param1 IS NULL) AND (([Field2] = @param2) OR @param2 IS NULL)

    etc.

    • I myself use this trick. Only @param1 IS NULL best placed before [Field1] = @param1 . In order for the optimizer in the case of @param1 IS NULL = FALSE eliminate the processing [Field1] = @param1 . - KiTE
    • one
      Will not help. The server in order a) compiles the request, BE) takes the parameters ve) executes the request and so, compiling the request will give a plan where you will first scan for [Field1] = @ param1, and then for each line calculate @ param1 IS NULL. The correct solution in this case is to apply the RECOMPILE hint. Then the server will first simplify the condition, and then make a new execution plan. SELECT * FROM [Table] WHERE .. OPTION (RECOMPILE) - renegator
    • I clarify that I am talking about MS SQL. - renegator