Skip to content

Using COALESCE to avoid using sp_executeSql

Last updated on October 2, 2020

When creating stored procedures, we sometimes have to include null or optional parameters. We are then forced 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:[JS_CODE_0] [JS_CODE_1]

//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

Comments are closed, but trackbacks and pingbacks are open.