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

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 g开发者_如何学运维one 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

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)

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;

I use this method quite frequently:

DECLARE @YEAR AS VARCHAR(4)   SET @YEAR = 2015
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @OPENQUERY AS VARCHAR(MAX)
DECLARE @LINKEDSERVER AS VARCHAR(MAX) SET @LINKEDSERVER = 'Name of Linked Server here with out brackets'

SET @SQL='
Select 
tbl1.* 
FROM 
dbo.Table_ON_LINKED_SERVER AS tbl1 
WHERE 
tbl1.number_id = ''''1'''' 
AND YEAR(tbl1.DATETIME) = ' + @YEAR + '
AND tbl1.NAME <> ''''%JONES%''''
'''

SET @OPENQUERY = 'SELECT * INTO ##GLOBAL_TEMP_NAME FROM OPENQUERY(['+ @LINKEDSERVER +'],''' + @SQL + ')'
--SELECT @OPENQUERY
EXEC(@OPENQUERY)

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

With some care you could use a shared temp table:

DECLARE @Qry AS VARCHAR(MAX)
SET @Qry = 'select * into ##tempTable from openquery([the Linked server],''exec thelinkedSPname ' + @param1 + ''')'
EXEC (@Qry)

-- Now just use the shared Temp table, or I suppose you could copy it to a temp table just as you wanted it:

SELECT * INTO #tempTable FROM( SELECT * FROM ##tempTable)tbl
DROP TABLE ##tempTable