存储过程，并使用参数从链接的存储过程中填充临时表 [英] Stored Procedure and populating a Temp table from a linked Stored Procedure with parameters

问题描述

我有一个存储过程(SP)，我在其中传递了一个值.在此SP中，我试图根据链接/远程服务器上另一个SP的结果创建/填充临时表.那就是我试图在我的SP中执行一个SP并填充查询将使用的临时表.

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.

我尝试使用以下语法，但由于openquery似乎不喜欢"+"或@ param1参数，因此无法正常工作.

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')

我还进行了手动构建临时表并尝试执行链接的SP的尝试，但是效果不佳.

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

关于如何从通过链接服务器执行SP的SP中填充临时表的任何建议.注意上面的SQL只是伪代码

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

推荐答案

两个词:动态查询. 试试这个:

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

这篇关于存储过程，并使用参数从链接的存储过程中填充临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！