Solved

Passing a parameter while looping into OpenQuery

Posted on 2004-10-14
12
9,534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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