Stored Procedure and populating a Temp table from a linked Stored Procedure with parameters

Question

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.

I have tried using the following syntax, but it does not work as it seems openquery does not like the "+" or the @param1 parameter.

select * into #tempTable
from openquery([the Linked server],'exec thelinkedSPname ' + @param1)

If I have the parameter value hard coded in this it works fine.

select * into #tempTable
from openquery([the Linked server],'exec thelinkedSPname 2011')

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.

create table #tempTable(
.
.
.
)

insert into #tempTable
(
.
.
.
)
Exec [the Linked server],'exec thelinkedSPname ' + @param1

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

Answer 1

Two words: Dynamic Query. Try this:

DECLARE @TSQL varchar(8000)
SELECT  @TSQL = 'SELECT * INTO #tempTable FROM OPENQUERY([the Linked server],''exec [the Linked server].DBName.dbo.thelinkedSPname ' + @param1 + ''')'
EXEC (@TSQL)

This is well documented here: How to pass a variable to a linked server query