title: Using UDF in SELECT statement
body: <p>I made a user-define function for business hours calculation.</p>
  
  <p>This is my UDF.</p>
  
  <pre><code>CREATE FUNCTION  fn_GetBusinessHour (@date datetime, @addHours int)
  RETURNS datetime
  AS
  BEGIN 
      DECLARE @CalcuatedDate datetime;
      DECLARE @addDayCount int, @addHourCount int, @addMinCount int;
  
      SET @addDayCount = @addHours / 8.5;
      SET @addHourCount = @addHours - (@addDayCount * 8.5);
      SET @addMinCount =  @addHours - (@addDayCount * 8.5) - @addHourCount;
  
      IF(@addDayCount != 0) 
          SET @CalcuatedDate = DATEADD(DD, @addDayCount, @date); 
  
      SET @CalcuatedDate = DATEADD(HH, @addHourCount, @CalcuatedDate);
  
      IF(@addMinCount != 0) 
          SET @CalcuatedDate = DATEADD(MM, @addMinCount, @CalcuatedDate); 
  
      RETURN @CalcuatedDate;
  END
  </code></pre>
  
  <p>When I test using following statement,</p>
  
  <pre><code>SELECT dbo.fn_GetBusinessHour(GETDATE(), 40)
  </code></pre>
  
  <p>It shows proper result.</p>
  
  <p>However, I use my function like this,</p>
  
  <pre><code>SELECT TicketID
       , DateTimeLogged --Type: Datetime
       , Priority       --Type: int
       , [dbo].[fn_GetBusinessHour](DateTimeLogged, Priority)
    FROM TicketHeader
  </code></pre>
  
  <p>the result shows only NULL value.</p>
  
  <pre><code>TicketID    DateTimeLogged  Priority    (No column name)
  1   2011-07-04 11:26:19.510     30  NULL
  2   2011-07-04 13:58:45.683     30  NULL
  3   2011-07-05 10:09:16.923     10  NULL
  4   2011-07-05 13:13:30.237     30  NULL
  5   2011-07-05 16:50:34.033     20  NULL
  </code></pre>
  
  <p>I tried CONVERT because it worked when I give a value 40 but it also shows null values.</p>
  
  <pre><code>SELECT TicketID
           , DateTimeLogged --Type: Datetime
           , Priority       --Type: int
           , [dbo].[fn_GetBusinessHour](DateTimeLogged, CONVERT(int, Priority))
        FROM TicketHeader
  </code></pre>
  
  <p>How can I fix this to work my UDF?
  Why this thing happen?
  I cannot understand what is different between Priority and 40.</p>
  
  <p>Thank you in advance.</p>