Skip to content

Using COALESCE to avoid using sp_executeSql

Last updated on April 12, 2022

Creating stored procedures for table searches is simple with where clause. The difficulty arises when we have to include null or optional parameters for the search. We have to manage the situation where the parameter is null and the stored procedure call must have the parameter name defined. 

To handle this we pull out the old sp_executeSql, and solve the problem. But if the search criteria are not too complex, we can use COALESCE as shown below to handle the problem:

//one line comment
/* 
 * multiline 
 * comments
 */
SELECT firstname
    ,lastname
    ,phone
FROM customer c
WHERE COALESCE(c.firstname, '') = COALESCE(@first, c.firstname, '')
    AND COALESCE(c.lastname, '') = COALESCE(@last, c.lastname, '')
    AND COALESCE(c.phone, '') = COALESCE(@phone, c.phone, '') 

One caution to remember is COALESCE is not sargable. Sargable? Sargable is the concatention of : search, argrument and able. According to wikipedia SARGable is defined as “In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The advantage of sargable queries are:

  • Consuming less system resources
  • Speeding up query performance
  • Using indexes more effectively