title: SQL Server : cannot get columns to output to varchar
body: <p>I'm having an odd issue. I have <code>uid</code> and <code>ui2</code> columns that I combine into a <code>key</code> column. Most of the time the uids are an alphanumeric combination and I don't have any issues.</p>
  <p>However this set of data the uids are all numeric:</p>
  <p>Input data:</p>
  <pre><code>+-------+-------+
  | uid   | uid2  |
  +-------+-------+
  | 12345 | 98765 |
  | 45645 | NULL  |
  +-------+-------+
  </code></pre>
  <p>What the output SHOULD look like:</p>
  <pre><code>+-------+-------+------------+
  | uid   | uid2  | key        |
  +-------+-------+------------+
  | 12345 | 98765 | 1234598765 |
  | 45645 | NULL  | 45645      |
  +-------+-------+------------+
  </code></pre>
  <p>If the <code>uid</code> is not NULL then it's fine, however, if it is NULL, then it adds a 0 to the end of the uid.  So where <code>uid=45645</code> I end up with <code>456540</code></p>
  <p>When I combined the two columns, I used <code>CAST AS varchar(255)</code>.</p>
  <p>I also tried to then <code>CAST</code> the entire result.  Neither worked.</p>
  <p>Here is my code:</p>
  <pre><code>SELECT 
      [uid] AS [id], 
      [uid2] AS [id2], 
      (CAST([uid] AS varchar(255)) + CAST(IIF([uid2] IS NULL, ' ', [uid2]) AS varchar(255))) AS [key], 
      (CAST(CAST([uid] AS varchar(255)) + CAST(IIF([uid2] IS NULL, ' ', [uid2]) AS varchar(255)) AS varchar(255))) AS [key]
  FROM 
      [test]
  </code></pre>
  <p>And that is the output:</p>
  <pre><code>+-------+-------+------------+------------+
  | uid   | uid2  | key        | key        |
  +-------+-------+------------+------------+
  | 12345 | 98765 | 1234598765 | 1234598765 |
  | 45645 | NULL  | 456450     | 456450     |
  +-------+-------+------------+------------+
  </code></pre>
  <p>The <code>IIF</code> is to deal with the NULL. Otherwise it NULLs the entire thing. If I replace the ' ' with '-' then I get</p>
  <blockquote>
  <p>Error converting data type varchar to float</p>
  </blockquote>
  <p>which makes me think that even though everything is cast to varchar it's still numeric.</p>
  <p>As a work around I could probably remove the last 0 from keys were uid2 is NULL, but that's kind of 'hacky' and I'd really like to know what's going on here.</p>