Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql server - 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 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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...