Check the Column Is NULL or NOT in SQL SERVER

Question

I have a procedure in SQL Server 2008 R2, I want to enter the data to vol_Hours column and check it before if it is not null then plus the entry with old data that it in the column, if it's NULL then add the entry to the column without plus the NULL value. I cannot add 2+NULL because it's = NULL. MY Code Is:

 create procedure updateVolunteerHours @vol_ID int, @vol_Hours int As if vol_Hours is NULL -- vol_Hours it is the Column Name Update Personal_Information set vol_Hours = @vol_Hours where vol_ID = @vol_ID else Update Personal_Information set vol_Hours = @vol_Hours + vol_Hours where vol_ID = @vol_ID

user2864740 · Accepted Answer · 2013-10-25 20:43:32Z

5

In this case, just update the adding expression to use COALESCE (or ISNULL or CASE) and remove the IF statement entirely.

Update Personal_Information set vol_Hours = COALESCE(vol_Hours, 0) + @vol_Hours where vol_ID = @vol_ID

If both branches did entirely different things, then the conditional would have to be altered to use the results of a query.

IF EXISTS(SELECT * FROM Personal_Information WHERE vol_ID = @vol_ID AND vol_Hours IS NULL) ..

.. but that's just not needed here.

edited Oct 25, 2013 at 20:43

answered Oct 25, 2013 at 20:38

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Aaron Bertrand Over a year ago

Just for some added reading material, in case anyone is bored, I wrote an article about COALESCE vs. ISNULL.

Abdullah Bahattab Over a year ago

I'm going to put your post as the Answer but let me see if someone has another solution. I just want it as my post why it's not working

Abdullah Bahattab Over a year ago

@user2864740| can I choose one column and make the default value is 0?

user2864740 Over a year ago

@AbdullahBahattab Sure, if you don't need NULL values: 1) set the DEFAULT VALUE to 0; then 2) make the column NOT NULLABLE (all values in the column that are currently NULL will be assigned the DEFAULT VALUE). You will need to investigate any usage sites to make sure that nothing broke :)

Abdullah Bahattab Over a year ago

@user2864740| I know NUMBER 2 but I dont know the NUMBER 1, how set the DEFAULT VALUE to 0?

|

Collectives™ on Stack Overflow

Check the Column Is NULL or NOT in SQL SERVER

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related