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
Solved

Passing a parameter while looping into OpenQuery

Posted on 2004-10-14
12
9,515 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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