2

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
user
  • 6,897
  • 8
  • 43
  • 79
Abdullah Bahattab
  • 612
  • 1
  • 16
  • 32

1 Answers1

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.

user2864740
  • 60,010
  • 15
  • 145
  • 220
  • 1
    Just for some added reading material, in case anyone is bored, I wrote [an article about COALESCE vs. ISNULL](http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/). – Aaron Bertrand Oct 25 '13 at 20:46
  • 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 Oct 25 '13 at 20:56
  • @user2864740| can I choose one column and make the default value is 0? – Abdullah Bahattab Oct 25 '13 at 21:06
  • @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 :) – user2864740 Oct 25 '13 at 21:08
  • @user2864740| I know NUMBER 2 but I dont know the NUMBER 1, how set the DEFAULT VALUE to 0? – Abdullah Bahattab Oct 25 '13 at 21:09
  • @AbdullahBahattab I usually use the SSMS Table Designer (because I'm lazy and don't know all the commands off the top of my head :-/). – user2864740 Oct 25 '13 at 21:10
  • @user2864740| I found it: [HERE](http://stackoverflow.com/questions/6791675/how-to-set-a-default-value-for-an-existing-column) – Abdullah Bahattab Oct 25 '13 at 21:16
  • @AbdullahBahattab Yay! You'll also need to make the column NOT NULL. – user2864740 Oct 25 '13 at 21:19