title: SQL Server - Pass Table as Parameter to function within select
body: <p>Have setup a TableType as </p>
  
  <pre><code>create type KeyPairTable as Table (KeyValue nvarchar(50), 
                                     [Description] nvarchar(500))
  </code></pre>
  
  <p>I have a table which can be simplified to</p>
  
  <pre><code>create table Elements (SharedKey int,
                         ElementName nvarchar(50), 
                         SourceSQL nvarchar(500))
  </code></pre>
  
  <p>and I have a function with header</p>
  
  <pre><code>create function RunElement(@SharedKey int,
                             @ValuesTable KeyPairTable readonly)
  </code></pre>
  
  <p>now what I need to do is effectively:</p>
  
  <pre><code>select RunElement(SharedKey, 
                    case when SourceSql is not null then [X] 
                         else [Y] 
                    end) from Elements
  </code></pre>
  
  <p>where [X] is taking the SQL statement stored in SourceSQL and passing it in as a KeyPairTable and [Y] is an empty table (e.g. it won't be used / isn't needed)</p>
  
  <p>I would like to avoid cursors / loops and do this in a select as this is a simplification of the problem and the select actually joins several tables.  </p>