THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

A variation of this question popped up twice in the SQL programming newsgroup since yesterday, this means it is time for a blogpost.
Let's say you have a proc like this


CREATE PROC prUpdateTable
@Salesman
int = -1
AS

..........

If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this


UPDATE table
SET
Column = COALESCE(NULLIF(@variable,-1),Column)


Here is some code to demonstrate that


CREATE TABLE #foo (id int,salesman int)
INSERT #foo VALUES(1,1)
INSERT #foo VALUES(2,1)
INSERT #foo VALUES(3,1)
INSERT #foo VALUES(4,1)
 
 
 
SELECT * FROM #foo
-------------
1 1
2 1
3 1
4 1
 
DECLARE @salesman int
SELECT
@salesman = 5
 
--Column value will change to 5
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =1
 
--Column value won't change
SELECT @salesman = -1
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =2
 
--Column value won't change
SELECT @salesman = NULL
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =3
 
--Column value will change to 3
SELECT @salesman = 3
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =4
 
--And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
SELECT * FROM #foo
-------------
1 5
2 1
3 1
4 3
 
DROP TABLE #foo



As you can see only the first and the last update statement changed the value of the salesman column
Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.


 

Published Friday, March 28, 2008 11:43 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

v.c. said:

UPDATE #foo

SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)

WHERE ID =1

if @salesman equals -1 or null, the sql statement should equals:

update #foo set salesman=salesman where id=1

logically, this column is not changed, but sql server looks like don't know this, checking the execution plan we can see the cost of Table_Update.

so, in formal ways we check parameters by IF can prevent unnecessary operation. right?

March 29, 2008 4:53 PM
 

noeld said:

Although you may think that update <table> set col = col is harmless I think it is a *bad* idea.  If an index happens to exists on such column you incurr in IO so It is wiser to simply include the check on the "where" predicate rather than on the SET.

So it is much better to do:

update #foo set salesman=@salesman where id=1 and @salesman > 0

Just My $0.02

March 31, 2008 2:29 PM
 

Denis Gobo said:

My example was simple, and I did say that you would not use this for one column

you would use it for something like this

UPDATE #foo

SET salesman = COALESCE(NULLIF(@salesman,-1),salesman),

country = COALESCE(NULLIF(@country,-1),country),

col1 = COALESCE(NULLIF(@col1,-1),col1),

col2 = COALESCE(NULLIF(@col2,-1),col2),

col3 = COALESCE(NULLIF(@col3,-1),col3),

col4 = COALESCE(NULLIF(@col4,-1),col4)

WHERE ID =3

April 1, 2008 10:32 AM
 

Ravindar said:

1 1 999615113 A BNK INFO ----- ICICI

2 2 999615113 A BNK INFO ICICI HDFC

3 3 999615113 A BNK INFO HDFC AXIS

4 4 999615113 A BNK INFO AXIS SBI

5 5 999615113 C BNK INFO SBI JK

6 6 999615113 C BNK INFO JK HSBC

7 7 999615113 C BNK INFO HSBC AMERICAN BANK

8 8 999615113 C BNK INFO AMERICAN BANK RBS

i want o/p this like plz can u guide me immidiatly, i am waiting for u r ans.

April 10, 2008 3:15 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement