title: Stored Procedure and populating a Temp table from a linked Stored Procedure with parameters
body: <p>I have a Stored Procedure (SP) in which I pass in one value. In this SP, I am trying to create/populate a Temp Table from the result of another SP that is on a Linked/remote server. That is I am trying to executute an SP in my SP and populate a temp table which my query will use.</p>
  
  <p>I have tried using the following syntax, but it does not work as it seems openquery does not like the "+"  or the @param1 parameter.</p>
  
  <pre><code>select * into #tempTable
  from openquery([the Linked server],'exec thelinkedSPname ' + @param1)
  </code></pre>
  
  <p>If I have the parameter value hard coded in this it works fine.</p>
  
  <pre><code>select * into #tempTable
  from openquery([the Linked server],'exec thelinkedSPname 2011')
  </code></pre>
  
  <p>I have also gone as far as manually building the temp table and trying to execute the linked SP but that does not work as well.</p>
  
  <pre><code>create table #tempTable(
  .
  .
  .
  )
  
  insert into #tempTable
  (
  .
  .
  .
  )
  Exec [the Linked server],'exec thelinkedSPname ' + @param1
  </code></pre>
  
  <p>Any suggestions as to how to populate a temp table from within a SP that executes a SP via a linked server. Note the above SQL is only pseudo  code</p>