OpenQuery variable as a parameter

Question

Hi All,

 

I created a command to get resultset of a procedure by using openquery. code is following

 

SELECT * into #tmp FROM OPENQUERY(LinkSrv,'exec db1.dbo.proc1 0')

 

Here: LinkSrv = link server, db1 = database, proc1 = procedure to execute, 0 = parameter of procedure

 

This command is running successfully. but i don't want to hard code procedure name and the parameters so i changed my query like:

 

declare @Parameter nvarchar(500)

Set @Parameter = 'exec db1.dbo.proc1 0'

SELECT * into #tmp FROM OPENQUERY(LinkSrv, @Parameter)

 

by doing this i got an error which is:

 

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '@Parameter'.

-------------------------------------------------------------------------------------------

 

Because OpenQuery didn't support the variable as a parameter. then i got a solution for this issue and made my query like this:

 

Set @SQLStr = 'SELECT * into #tmp FROM OPENQUERY(LinkSrv,''' + @Parameter+ ''')

exec (@SQLStr)

 

Query executed successfully but i got another issue that i am not able to access temp table #tmp out of this dynamic query. off-course i can't because it was created in dynamic sql. but i need this temp table outside of this dynamic query to perform many operations with some XML commands which can't be take place in the same dynamic query. Now im stuck-up with this issue. if any buddy knows how to resolve these issues then please help me. I don't want to use permanent table also.

 

Thanks in advance

Muhammad Adil Aleem

Answer 1

one way is to create the table first

 

 

Code Snippet

create table #tmp(.....)

 

Set @SQLStr = ' INSERT into #tmp SELECT * FROM OPENQUERY(LinkSrv,''' + @Parameter+ ''')'

exec (@SQLStr)

 

 

 

 

 

Denis The SQL Menace

http://sqlservercode.blogspot.com

http://sqlblog.com/blogs/denis_gobo/default.aspx

http://www.lessthandot.com/

Answer 2

 

Thanks for your reply. but I even don't know that what will be the axact columns of the table because I want to make a generic query in which I can pass any procedure name and it will return the resultset of that procedure. Any help.

Answer 3

why do you need a temp table then?

do this

 

Set @SQLStr = ' SELECT * FROM OPENQUERY(LinkSrv,''' + @Parameter+ ''')'

exec (@SQLStr)

 

 

Denis The SQL Menace

http://sqlservercode.blogspot.com

http://sqlblog.com/blogs/denis_gobo/default.aspx

http://www.lessthandot.com/

 

Answer 4

Because i have to do some XML operations on the resultset. and this will be possible when my query return me a table.

Answer 5

Then use global temp table

---

Naresh