Passing a parameter while looping into OpenQuery

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.

"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)

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'.

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

OpenQuery(AS400, ''@MyQuery'')

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

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

"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?

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%''')

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

Anyone at all want to help to answer this last question?

By the way I just put this into a Stored Proc and it worked perfectly.