Solved

Passing a parameter while looping into OpenQuery

Posted on 2004-10-14
12
9,482 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

759 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

18 Experts available now in Live!

Get 1:1 Help Now