title: Execute Stored Procedure or Dynamic SQL in a Function
body: <p>I am using SQL Server 2012 and am trying to convert data in a temp table to another temp table, converting values as I copy the info from one table to another.  Here is an example of the input table:</p>
  
  <pre class="lang-none prettyprint-override"><code>#IMPORT_DATA
  SSN             Plan                          StartDate
  123-45-6789     Basic Life Insurance          1/1/2015
  123-45-6789     Vision                        1/1/2015
  123-45-6789     Dental                        1/1/2015
  </code></pre>
  
  <p>I need to convert this data to something like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>#STAGE_DATA
  SSN             Plan                          StartDate
  123456789       BLI                           20150101
  123456789       VIS                           20150101
  123456789       DTL                           20150101
  </code></pre>
  
  <p>I have a translation table that defines a SQL script that will convert the data but am having an issue with executing dynamic SQL from within a function.  </p>
  
  <p>I am using a table-valued function to load <code>#STAGE_DATA</code>, passing in the <code>#IMPORT_DATA</code> as XML.  The table function is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>create function dcBPI.TranslateBenefitsStagingTable_FN
  (
        @XmlData xml
      , @TranslationType varchar(50)
      , @Company varchar(3) = null
  )
  returns @ReturnTable table (
        [RowID] [int]
      , [SSN] [varchar](9) NULL
      , [Plan] [varchar](3) NULL
      , [StartDate] [varchar](8) NULL
  )
  as
  
  begin
  
      insert into @ReturnTable ([RowID], [EmpSSN]) 
      select [Table].[Column].value('ID[1]', '[int]') as 'RowID'   -- no translation for RowID
              , dcBPI.TranslateSourceValue_FN('Benefit', 'SSN', [Table].[Column].value('SSN[1]', '[varchar](11)'), @Company) as 'SSN'
              , dcBPI.TranslateSourceValue_FN('Benefit', 'Plan', [Table].[Column].value('Plan[1]', ' [varchar](3)'), @Company) as 'Plan'
              , dcBPI.TranslateSourceValue_FN('Benefit', 'StartDate', [Table].[Column].value('StartDate[1]', ' [varchar](10)'), @Company) as 'StartDate'
  
      from @XmlData.nodes('//row') as [Table]([Column])
  
      return 
  end
  go
  </code></pre>
  
  <p>The translation function is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>create function dcBPI.TranslateSourceValue_FN
  (
        @TranslationType varchar(50)
      , @DestinationHeader varchar(255)
      , @SourceValue varchar(255)
      , @Company varchar(3) = null
  )
  returns varchar(255)
  as
  begin
      declare   @DestinationValue varchar(255) = 'Missing'
              , @sql nvarchar(max) = ''
  
      if len(rtrim(@Company)) = 0 set @Company = null
  
      select  @sql = 
              select
                      distinct td.DestinationValue
              from    dcBPI.TranslationData td    
              where   (
                          td.Company = @Company 
                          or td.Company = 'All'
                      )
                      and td.TranslationType = @TranslationType
                      and td.DestinationHeader = @DestinationHeader
                      and td.SourceValue = @SourceValue
  
      )
  
      exec sp_executesql @sql, N'@OutputValue nvarchar(max) OUTPUT', @OutputValue = @DestinationValue OUTPUT
  
      return @DestinationValue
  
  end
  go
  </code></pre>
  
  <p>When I execute the table-valued function (which in turn executes <code>TranslateSourceValue_FN</code>), I get an error message of:</p>
  
  <blockquote>
    <p>Only functions and some extended stored procedures can be executed
    from within a function.</p>
  </blockquote>
  
  <p>I know from other articles that I cannot call Dynamic SQL from a function, so is there another way to accomplish what I am trying to do?</p>
  
  <p><strong>EDIT</strong> (added @sql examples)<br>
  The SQL that is to be used are simple select statements like:</p>
  
  <ul>
  <li><code>select replace('123-45-6789' '-', '')</code></li>
  <li><code>select PlanCode from CodeTable where Plan = @Plan'</code></li>
  </ul>