title: Why does the column-type of the query change depending on its values?
body: <p>I have a stored function that calculates a price and returns it as a float:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[product_get_GrossPrice] (@pid INTEGER) 
  RETURNS FLOAT
  AS BEGIN
      DECLARE @ret AS FLOAT 
  
      SELECT
          @ret = (ROUND((price * (CAST(1 AS FLOAT) + (CAST(ISNULL(tax, 0) AS FLOAT) / CAST(100 AS FLOAT)))), 0))
      FROM
          products
      WHERE
          id = @pid
  
      RETURN @ret
  END
  </code></pre>
  
  <p>This function is used in a query like this:</p>
  
  <pre><code>SELECT
      p.*, dbo.product_get_GrossPrice(p.id) as gross_price
  FROM 
      products
  ORDER BY 
      id
  </code></pre>
  
  <p>On the web server the following code is used to read the column <code>gross_price</code>:</p>
  
  <pre><code>double? grossPrice = dataRow.Field&lt;double?&gt;("gross_price");
  </code></pre>
  
  <p>This works fine until the query returns only 0s and 1s in the column. It seems that if this is the case the column automatically turns into a boolean and the cast fails.</p>
  
  <p>So what I did to solve this is that I changed the query to this:</p>
  
  <pre><code>select p.*, cast(dbo.product_get_GrossPrice(p.id) as float) as gross_price
  from products
  order by id
  </code></pre>
  
  <p>I've never encountered this problem before and it gives me a strange feeling in the stomach.</p>
  
  <p><strong>Question:</strong> what is going on here and do I have to cast every column to its desired data type to be sure that the casts won't break?</p>