title: SELECT statement needs to output value conditional on NULL data
body: <p>It was hard to condense in the title what I need... Here it goes. This is for SQL Server 2008.</p>
  
  <p>For a reason that is too long to explain here, I need to have a SELECT statements that will display, among other columns, a value of 1 if 4 columns have a value null. Something like this.</p>
  
  <pre><code>SELECT name, lastname, p1, p2, p3, p4, "1 if p1,p2,p3 and p4 are all NULL, 0 otherwise" city, st, zip
  FROM mytable
  </code></pre>
  
  <p>The questions is how to I translate to code this: <strong>"1 if p1,p2,p3 and p4 are all NULL, 0 otherwise"</strong> ?</p>
  
  <p>I tried </p>
  
  <pre><code>CASE len(p1+p2+p3+p4) WHEN NULL 1 ELSE 0 END
  </code></pre>
  
  <p>But it didn't work. even though when I do</p>
  
  <pre><code>SELECT len(p1+p2+p3+p4) FROM mytable
  </code></pre>
  
  <p>The result iS NULL.</p>
  
  <p>I tried IF ELSE, but it doesn't seem to be welcome in a SELECT statement.</p>
  
  <p>Ideas?</p>
  
  <p>Thanks.</p>
  
  <hr>
  
  <p>Thanks guys.
  I chose the answer that doesn't use COALESCE because it's the one I understood. I tried reading MS's explanation here: <a href="http://msdn.microsoft.com/en-us/library/ms190349.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms190349.aspx</a> and I still didn't fully understand, plus the note saying that "At least one of the null values must be a typed NULL" scared me off a bit.
  Thanks to all.</p>
  
  <hr>