Using the TSQL COALESCE function

So, I've done about a couple dozen procs where I've needed to pass in one or more pieces of data as filtering criteria. For example, if I needed to do a search against invoices some possible filter values would be date range, invoice number, invoice total, or invoice status type. Before I discovered the very cool TSQL COALESCE function I would build my SQL... dynamically! Yep, you read that right... DYNAMICALLY!!! For those with no clue what I'm talking about, peep this SQL block:

Search Proc (Ugly Version)

//Ugly search SQL... do not attempt at home!
CREATE PROCEDURE [dbo].[UserSelectBySearch]
(
    @SearchWord varchar(50)
)
AS

    -- Local variables
    DECLARE @SQL nvarchar(1000)

    -- Create Search SQL
    SET @SQL = 'SELECT
                    USR.UserID,
                    USR.UserName,
                    USR.FirstName,
                    USR.LastName
                FROM
                    Users USR
                WHERE
                    USR.UserName LIKE ''' + @SearchWord + '''
                    OR USR.FirstName LIKE ''' + @SearchWord + '''
                    OR USR.LastName LIKE ''' + @SearchWord + ''''

    -- Execute created SQL.
    EXEC sp_ExecuteSQL @SQL


There are several glaring problems with the above SQL. First is that it's a real pain in the rear to debug. You have to make use of the PRINT @SQL to see the actual statement that's being executed! Second is that it's not the most efficient. Lastly, it's prone to error. If I or someone else had to add another few dozen columns to return or add some additional search criteria, it's possible the size of the string would exceed the set 1000 characters... resulting in an error when executed. In any case, it's just not a very nice way to write a search proc. Now let's take a look at the same thing using the very cool COALESCE function.

Search Proc (Pretty COALESCE Version)

//Pretty COALESCE search SQL... please attempt at home!
CREATE PROCEDURE [dbo].[UserSelectBySearch]
(
    @SearchWord varchar(50) = NULL
)
    -- Search SQL using COALESCE evaluation function
    SELECT
              USR.UserID,
              USR.UserName,
              USR.FirstName,
              USR.LastName
      FROM 
            Users USR
      WHERE
        USR.UserName LIKE COALESCE(@SearchWord, USR.UserName)
        OR USR.FirstName LIKE COALESCE(@SearchWord, USR.FirstName)
        OR USR.LastName LIKE COALESCE(@SearchWord, USR.LastName)


So, what is COALESCE doing in the above proc? It's evaluating the value of the search keyword against the specified column to determine if a match is found. If it evaluates to a NONNULL, then SQL returns the row of data since it's a match. Let's see a couple of examples.

An example using COALESCE
In our examples, we'll have a database with 4 users. See the database below for user details.

UserID	UserName	FirstName	LastName
1	Avalon	David	Nicolosi
2	Avalon24	David	Doe
3	Jabba	Da	Hutt
4	Mr	McMonkey	McBean

-- EXAMPLE #1: Pull all users who's First Name is David
sp_execute UserSelectBySearch 'David'

-- Result (2 results)
UserID    UserName    FirstName  LastName
--------------------------------------------------------------------
1         Avalon      David      Nicolosi
2         Avalon24    David      Doe

-- EXAMPLE #2: Pull all users from the database
sp_execute UserSelectBySearch

-- Result (2 results)
UserID    UserName    FirstName  LastName
--------------------------------------------------------------------
1         Avalon      David      Nicolosi
2         Avalon24    David      Doe
3         Jabba       Da         Hutt
4         Mr.         McMonkey   McBean


As you can see from the results, the COALESCE proc returns exactly the data that we expect. It's a much cleaner implementation than the first example and one that I'm sure you'll use often.

More information

---

David Nicolosi
Drop-In Code Back to index