title: query templates while Linked Servers handling
body: <p>In order to make single interface for app getting data from different datasources I created one stored procedure where I try to keep all data access/handling routines.
  Current situation:</p>
  
  <ul>
  <li>Sql Server sp receives fixed number of parameters and should return recordsets taken from remote servers;  </li>
  <li>remote servers represented as Linked Servers based on Sql Sqrver Express both sides;</li>
  <li>some Linked Servers are just connection points lead to local OLE DB file datasources (others have db on Sql Server itself);</li>
  <li>remote data structures vary a lot and for each Linked Server should be kept special list of queries with variables handling;</li>
  </ul>
  
  <p>Example of query:  </p>
  
  <pre><code>select * from OPENQUERY(linkedA,
  'select f1 as id,f2 as name
  from  openrowset(''VFPOLEDB'',''c:\program\db\Table.FPT'';'''';
  '''',''SELECT *  FROM Table  where f1=1'')')
  </code></pre>
  
  <p>When source is Sql Server based:  </p>
  
  <pre><code>select * from OPENQUERY(linkedB,
  'select f3 as id,f4 as name from [db].[dbo].[Table] where f3=1')
  </code></pre>
  
  <p>As I need to handle not only connection properties (linked server name) but query parameters (db, scheme) and variables (declared and existing in local sp runtime) I have some problems:</p>
  
  <ol>
  <li>Making default queries is a mess - parameters and variables parsing makes code unreadable;</li>
  <li>Because of handling variables I possibly need to turn to <code>sp_executesql</code> to simplify the process;</li>
  <li>Not sure about the way of keeping query codes - should it be in <code>config_table</code> as xml or smth.</li>
  </ol>
  
  
  
  <pre><code>&lt;Config id="1" name="linkedB" db="dblinkedB" scheme="dbo" version="1.0.1"&gt;
    &lt;Actions&gt;
      &lt;Action id="0" name="Query 1"&gt;select * from OPENQUERY(linkedB,'select f3 as id,f4 as name from [db].[dbo].[Table] where f3=1')')&lt;/Action&gt;
    &lt;/Actions&gt;
  &lt;/Config&gt;
  </code></pre>
  
  <p><strong>Question:</strong>
  Is there any bestpractices available?<br>
  According to subject of this topic I am looking for best way to organize query templates just passing them some parameters with possibility to use default or special code.</p>