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

I think you are gonna need dynamic SQL, since you can't pass the parameter to an OPENQUERY like that (but first visit this link) So you would have something like this:

create table #tempTable(
.
)

DECLARE @param1 VARCHAR(10), @Query VARCHAR(8000)
SET @param1 = '2011'
SET @Query = '
SELECT *
FROM OPENQUERY([Linked Server],''exec thelinkedSPname '' + @param1+''')'

INSERT INTO #tempTable
EXEC(@Query)

Answer 2

With the usual disclaimers about guarding dynamic SQL, you can do this without OPENQUERY etc. Just call sp_executesql remotely:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'EXEC thelinkedSPname ' + @param1 + ';';
INSERT #temptable EXEC [LinkedServerName].database.dbo.sp_executesql @sql;