title: convert 'null' varchar to decimal
body: <p>I have a requirement to create some xml structs (to borrow a C-phrase) in sql-server-2005. In order to do this, I change all my values to varchar. The problem arises when I want to make USE of these values, i have to convert them to decimal.</p>
  
  <p>So, my xml code looks like this:   </p>
  
  <pre><code>set @result = @result + &lt;VAL&gt;' + coalesce(cast(@val as varchar(20)), '-.11111') + '&lt;/VAL&gt;'
  </code></pre>
  
  <p>this way, if VAL is null, I return a special decimal and I can check for that decimal. The drawback of doing this, is that I can't use coalesce on the other end when I use the value, I have to check if it converted value is equal to 0.
  like this:</p>
  
  <pre><code>case when cast(InvestmentReturn.fn_getSTRUCT(...args...).value('results[1]/VAL[1]', 'varchar(40)')as decimal(10,5)) = -.11111
  </code></pre>
  
  <p>Since performance is unacceptable right now, I thought one way to improve performance might be to use coalesce instead of using a nested case statement and checking the value for equality with my special 'null' equivalent.</p>
  
  <p>Any thoughts?</p>
  
  <p>also, i see that select cast('null' as decimal(10,5)) gives me:</p>
  
  <pre><code>Msg 8114, Level 16, State 5, Line 1
  Error converting data type varchar to numeric.
  </code></pre>