title: How to change this function to get the opendatasource server name dynamically
body: <p>I have the next function created, a simple union from two tables, one from the current server, and the other from a remote server:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[fn_AllData] ()
  RETURNS @tmp TABLE
      (
      Id      int,
      Name    varchar(100)
      )
  AS
  BEGIN
      INSERT INTO @tmp
          SELECT Id, Name 
          FROM Table1
  
      INSERT INTO @tmp
          SELECT Id, Name 
          FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=SERVER1;user id=xxx;password=yyy').Database1.dbo.Table1
  
      RETURN
  END
  
  --SELECT * FROM fn_AllData()
  </code></pre>
  
  <p>The function works fine in this way.</p>
  
  <p>Now I need to obtain the server name dynamically, from another parameters table.</p>
  
  <p>It should be easy like this:</p>
  
  <pre><code>DECLARE @SERVERNAME as varchar(20)
  
  SELECT @SERVERNAME = Value 
  FROM Parameters 
  WHERE Name = 'SERVERNAME'
  [...]
  
  INSERT INTO @tmp
      SELECT Id, Name 
      FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=' + @SERVERNAME + ';user id=xxx;password=yyy').Database1.dbo.Table1
  </code></pre>
  
  <p>But <code>Opendatasource</code> does not accept parameters.</p>
  
  <p>Is there any way to achieve my goal keeping my code in a function?</p>