Sql-server – Easier way to handle so many isnull() situation

nullsql serversql-server-2008t-sql

Is there any good way to avoid writing isnull() function so many times inside a stored procedure?

I have a stored procedure that uses the isnull() function almost 30 times, I think that I am missing a concept, but until I find better way to get my data I'll love to clean my code from so many isnull() functions.

Can I for while set SQL Server 2008 R2 to using null values as float 0? Most of my isnull() is just adding zero 0 value if there is no data so I can do math operations.

EDIT: I am not lazy, I just trying to clean my code and avoid select parts looks like this

select  
   vrsta.NAZIV
   ,isnull(sum(prod.prod_mpci),0) as prod_MPCI
   ,isnull(sum(isnull(mal_MPCI,0) + (vel_kol * isnull(mal_MPC,vel_VPC))),0) as lager_mpci
   ,isnull(sum(vel_NCI),0)+isnulL(sum(mal_NCI),0) as lager_nci
   ,sum(   case 
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) > isnull(prod.prod_kol,0) and isnull(dzn.dzn,'2010-01-01') < @dzu
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_MPC,vel_VPC)
                    else 0 end
            ) as visak_MPC
   ,sum(   case 
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) > isnull(prod.prod_kol,0) and isnull(dzn.dzn,'2010-01-01') < @dzu
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_NC,vel_NC)
                    else 0 end
            ) as visak_MPC
    ,sum(   case 
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) <= isnull(prod.prod_kol,0) 
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_MPC,vel_VPC)
                    else 0 end
            ) as manjak_MPC

    ,sum(   case 
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) <= isnull(prod.prod_kol,0) 
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_NC,vel_NC)
                    else 0 end
            ) as manjak_NC

Best Answer

No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.

Related Question