Solved

Passing a parameter while looping into OpenQuery

Posted on 2004-10-14
12
9,507 Views
Last Modified: 2012-06-27
Can I pass a variable into an OPENQUERY for a linked server?  

ie. OpenQuery(AS400, '
SELECT SUBNO, PERNO, cgrpid
FROM hmodta.hmembp
WHERE cgrpid = '@SomeVar'
')

Here is the complete code. Can someone help me?

DECLARE @SomeVar CHAR(6)
DECLARE myCursor CURSOR FOR
SELECT * FROM
OpenQuery(AS400, 'SELECT GRPID
FROM hmodta.hcovrp
WHERE gnam1 like ''SUPERVALU%''')

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SomeVar

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @SomeVar

SELECT count(*) FROM
OpenQuery(AS400, '
SELECT SUBNO, PERNO, cgrpid
FROM hmodta.hmembp WHERE cgrpid = @SomeVar
') as a
INNER JOIN
OpenQuery(WEBSRVR2,
'SELECT External_System_Key, User_ID
FROM mcnet..user_header')  AS p
ON a.SUBNO + a.PERNO = p.External_System_Key


FETCH NEXT FROM myCursor INTO @SomeVar
END

CLOSE myCursor
DEALLOCATE myCursor
0
Comment
Question by:vikingg97
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 1

Expert Comment

by:SirParadox
ID: 12310637
I personally Hate OpenQuery, and am more fond of 4 part named sql statements (select * from server.database.owner.tablename)

 To answer your question directly.. No, not that I am aware of.  If you attempt to say openquery(server, 'query' + @SomeVar)
 you get an error... even if you put your query w/ variables into a variable say @SQL_statement.. openquery(server, @SQL_statement) ..., that errors to.

If your linked server supports it, as in you know the 4 part name of the remote tables then I would suggest using a simpler query like (assuming as400 is the server, hmodta is the DB, and hmembp is the table, and dbo is the owner)

SELECT SUBNO, PERNO, cgrpid FROM as400.hmodta.dbo.hmembp WHERE cgrpid = '@SomeVar')

The only problems you run into is if the linked server refuses to tell you the details you need.. For example I am unable to use this query type w/ a linked mssql server since I can't seem to figure out the owner of the files as in SELECT * FROM Server.Database.Owner/scope/scheme.tablename.  But an OpenQuery/OpenRecordset works well.
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 12310651
"Can I pass a variable into an OPENQUERY for a linked server?"

Yes like this for example:  


DECLARE @SomeVar CHAR(6), @MyQuery varchar(256)

SET @MyQuery = 'SELECT SUBNO, PERNO, cgrpid FROM hmodta.hmembp WHERE cgrpid = ' + @SomeVar

OpenQuery(AS400, @MyQuery)



0
 
LVL 1

Expert Comment

by:SirParadox
ID: 12311256
I tried passing a variable to openquery which contained my sql_statement and it errors.

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@MyQuery'.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

Expert Comment

by:SNilsson
ID: 12311516

hmm, Im sure I have done this...try

OpenQuery(AS400, ''@MyQuery'')

(note it's single quotes, two on each side)
0
 
LVL 1

Accepted Solution

by:
SirParadox earned 300 total points
ID: 12311683
DECLARE @SomeVar VARCHAR(10), @MyQuery nvarchar(256)
SET @SomeVar='278'
SET @MyQuery = 'SELECT top 1 CounterID FROM nacas2.NACAS2.DBO.tblDevicePortcounters WHERE PortID = ' + @SomeVar
SELECT * FROM OpenQuery(nacas2, '@MyQuery')

Server: Msg 8180, Level 16, State 1, Line 4
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@MyQuery'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

exec sp_executesql @stmt=@MyQuery

CounterID  
-----------
505610372
(1 row(s) affected)

0
 
LVL 1

Author Comment

by:vikingg97
ID: 12311927
This I have found is not possible.  The only way to pass it is after where I join the two tables.  It takes a LOT longer.

Here is what I have.  Any suggestions for performance, and can I put this in a view?

DECLARE @SomeVar CHAR(6)
DECLARE myCursor CURSOR FOR
SELECT * FROM
OpenQuery(AS400, 'SELECT GRPID
FROM hmodta.hcovrp
WHERE gnam1 like ''SUPERVALU%''')

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SomeVar

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @SomeVar

SELECT count(*) FROM
OpenQuery(AS400, '
SELECT SUBNO, PERNO, cgrpid
FROM hmodta.hmembp
') as a
INNER JOIN
OpenQuery(WEBSRVR2,
'SELECT External_System_Key, User_ID
FROM mcnet..user_header')  AS p
ON a.SUBNO + a.PERNO = p.External_System_Key
WHERE a.cgrpid = @SomeVar

FETCH NEXT FROM myCursor INTO @SomeVar
END

CLOSE myCursor
DEALLOCATE myCursor

0
 
LVL 34

Expert Comment

by:arbert
ID: 12315330
"I personally Hate OpenQuery, and am more fond of 4 part named sql statements "

I agree, but the 4part convention doesn't accomplish passthru like openquery does.....

Why do you need th cursor?  What's the desired end result?
0
 
LVL 1

Author Comment

by:vikingg97
ID: 12315676
I just want to loop through the recordset and pass in the variable from

SELECT * FROM
OpenQuery(AS400, 'SELECT GRPID
FROM hmodta.hcovrp
WHERE gnam1 like ''SUPERVALU%''')
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 200 total points
ID: 12315799
But why the cursor?  Is there a chance you can do what you want with a set based operation?
0
 
LVL 1

Author Comment

by:vikingg97
ID: 12315903
I actually am very new to sql server, and that is all I knew how to do it?  A fellow employee told me to try it that way.  How else should I do this?  I am very open to any suggestions.  Here is the statement.  I also want to put this in a view if possible.

DECLARE @SomeVar CHAR(6)
DECLARE myCursor CURSOR FOR
SELECT * FROM
OpenQuery(AS400, 'SELECT GRPID
FROM hmodta.hcovrp
WHERE gnam1 like ''SUPERVALU%''')

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SomeVar

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @SomeVar

SELECT count(*) FROM
OpenQuery(AS400, '
SELECT SUBNO, PERNO, cgrpid
FROM hmodta.hmembp
') as a
INNER JOIN
OpenQuery(WEBSRVR2,
'SELECT External_System_Key, User_ID
FROM mcnet..user_header')  AS p
ON a.SUBNO + a.PERNO = p.External_System_Key
WHERE a.cgrpid = @SomeVar

FETCH NEXT FROM myCursor INTO @SomeVar
END

CLOSE myCursor
DEALLOCATE myCursor

0
 
LVL 1

Author Comment

by:vikingg97
ID: 12372255
Anyone at all want to help to answer this last question?
0
 
LVL 1

Author Comment

by:vikingg97
ID: 12478151
By the way I just put this into a Stored Proc and it worked perfectly.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem with SQL Script - Cannot call methods on char 2 19
SQL Server 2012 r2 - Varible Table 3 19
Help creating a spatial object in SQL Server 4 20
SQL Error - Query 6 24
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now