title: SQL - Calculating columns using dynamic functions
body: <p>I'm trying to create a set of data that I'm going to write out to a file, it's essentially a report composed of various fields from a number of different tables, some columns need to have some processing done on them, some can just be selected.</p>
  
  <p>Different users will likely want different processing performed on certain columns, and in the future, I'll probably need to add additional functions for computed columns.</p>
  
  <p>I'm considering the cleanest/most flexable approach to storing and using all the different functions I'm likely to need for these computed columns, I've got two ideas in my head, but I'm hoping there might be a much more obvious solution I'm missing.</p>
  
  <p>For a simple, slightly odd example, a Staff table:</p>
  
  <pre><code>Employee |    DOB           |   VacationDays
  Frank    |    01/01/1970    |   25
  Mike     |    03/03/1975    |   24
  Dave     |    05/02/1980    |   30
  </code></pre>
  
  <p>I'm thinking I'd either end up with a query like</p>
  
  <pre><code>SELECT NameFunction(Employee, optionID),
         DOBFunction(DOB, optionID),
         VacationFunction(VacationDays, optionID),
  from Employee
  </code></pre>
  
  <p>With user defined functions, where the optionID would be used in a case statement inside the functions to decide what processing to perform.</p>
  
  <p>Or I'd want to make the way the data is returned customisable using a lookup table of other functions:</p>
  
  <pre><code>ID  |    Name                  |   Description
  1   |    ShortName             |   Obtains 3 letter abbreviation of employee name
  2   |    LongDOB               |   Returns DOB in format ~ 1st January 1970
  3   |    TimeStampDOB          |   Returns Timestamp for DOB
  4   |    VacationSeconds       |   Returns Seconds of vaction time
  5   |    VacationBusinessHours |   Returns number of business hours of vacation
  </code></pre>
  
  <p>Which seems neater, but I'm not sure how I'd formulate the query, presumably using dynamic SQL? Is there a sensible alternative?</p>
  
  <p>The functions will be used on a few thousand rows.</p>
  
  <p>The closest answer I've found was in this thread:
  <a href="https://stackoverflow.com/questions/10557258/call-dynamic-function-name-in-sql">Call dynamic function name in SQL</a></p>
  
  <p>I'm not a huge fan of dynamic SQL, although in this case I think it might be the best way to get the result I'm after?</p>
  
  <p>Any replies appreciated,
  Thanks,
  Chris</p>