Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any good way to avoid writing so many times isnull() function inside sproc ?
I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I find better way to get my data ill love to clean my code from so many isnull() functions.
Can I for while set MSSQL 2008R2 server to using null values as float 0.
Moost of my isnull()-s adding just zero 0 value if there is no data so I can do maths 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
share|improve this question
1  
Well, why are you using so many isnull() functions? Your application should be able to deal with NULL just fine, or perhaps you can avoid this by not allowing NULL in the first place. NULL is not the same thing as 0, but if you're going to treat it that way, it may as well. –  Aaron Bertrand Apr 26 '13 at 14:09
    
@AaronBertrand I do calculations (+,-,*) inside TSQL but my tables are mostly Left Outer Joined and I have many nulls –  adopilot Apr 26 '13 at 14:13
    
Well there is no magic setting that turns all NULL values into zeroes, sorry. –  Aaron Bertrand Apr 26 '13 at 14:13
    
@AaronBertrand Thanx on that, I was hoping that I can set ANSII NULL param but looks like it ANSII NULL is not in common whit my problem –  adopilot Apr 26 '13 at 14:17
1  
ANSI_NULLS considers how NULL values are evaluated (e.g. WHERE col = NULL vs. WHERE col IS NULL). It has nothing to do with how NULL float values might be coerced into becoming zero. –  Aaron Bertrand Apr 26 '13 at 14:19

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer

I have a similar query showing a TON of encapsulating ISNULL statements. I need the null values to represent items that have not been touched (as many columns required distinct identifiers for alterations). I tried a million different things to get around it and in the end just ended up with an annoyingly long code laced with a bunch of ISNULLs. The important thing about the 'annoyingly long coded laced with a bunch of ISNULLs' is that it works perfect for me every time. I would suggest just finding a pattern to using them to clean up the look of the code and make it easier to navigate in general.

As far as the suggestion to use COALESCE, I wouldn't suggest it depending on the amount of records you are trying to return. I have read over and over again that COALESCE can cause a hit to performance, though there are some benefits.

http://stackoverflow.com/questions/7408893/isnull-vs-coalesce http://www.sqlservercentral.com/Forums/Topic832742-392-5.aspx#bm1352397

share|improve this answer
3  
All of the things you've read over and over again point to performance differential theories with no actual data to back them up. Have you read this link, where I actually do performance tests, and also explain the many other benefits? –  Aaron Bertrand Apr 26 '13 at 20:23

Dump isnulls into an inner query and use them outside maybe to avoid multiple isnulls?

SELECT A 
FROM (SELECT ISNULL (A, 0) FROM TABLEA) A
...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.