dans.blog: Dynamic SQL Updates Using COALESCE()

I tend to forget just how many commands are built-in to T-SQL. So often the stuff I do in the database is so basic, that I forget that there are often many different ways to approach the same problem and I often just forget that certain functions even exist.

I was reading through my SQL Server 2000 Unleashed book the other night, and came across the COALESCE() function—one of those functions I had forgetten about. In a nutshell, you can provide the function an unlimited number of arguments and the first argument which isn't equal to null will be the value return from the function. The funny thing is, I had been trying to debate on the best method to re-write a particular stored procedure and the COALESCE() ended up being a very elegant solution.

The problem I was faced with, is I wanted to write a single stored procedure that I could use to update any single column or combination of columns based upon the primary key. In the past, I've always just written out a nasty looking string and then passed that to the execute command or the sp_executesql stored procedure that's included in SQL Server.

The COALESCE() function provides a unique solution to this problem—one in which the query doesn't have to be dynamically re-written each time the stored procedure is called. By assigning a default value of null to an argument not supplied to the stored procedure, we can use the COALESCE function to update the field with either a value supplied to the stored procedure, or with the existing value. If the existing value of field is null, we're still fine because the COALESCE function returns null if arguments evaluate to null. Here's a mock stored procedure similiar to the one I actually wrote:

CREATE PROCEDURE spUpdate (
    @pKey int,
    @col1 varchar(255) = null,
    @col2 int = null,
    @col3 int = null,
    @col4 bit = null
) AS

UPDATE
    someTable
SET
    col1 = COALESCE(@col1, col1),
    col2 = COALESCE(@col2, col2),
    col3 = COALESCE(@col3, col3),
    col4 = COALESCE(@col4, col4)
WHERE
    pKey = @pKey

GO

The disadvantage to this technique is that you will be cause an update to each column specified—even it's it just updating the value with itself. However, in many cases this performance hit is going to be neglegible and this method has other benefits (such as not flushing valuable stored procedures out of the cache, just for a dynamic single query.)

I'm curious if any of you have found a better technique than that above. If you have any comments, please post them.

Categories: SQL, HTML/ColdFusion

3 Comments

Steve Davis
Jun 18, 2005 @ 11:17 AM

Very good approach. It helped me simplify code and SQL.
Cheshire Web Design
Jan 26, 2012 @ 9:56 AM

A nice simple stored procedure to demonstrate COALESCE.

Useful, cheers.
Barreljack
Jul 28, 2014 @ 1:34 PM

Useful until you have to set a column to NULL. Then what do you do?

Comments for this entry have been disabled.