passing parameters to an OPENQUERY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

passing parameters to an OPENQUERY

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 07:21:45

hello,
how can I pass a value to an open query ?

like :

@ID = 10

SELECT * FROM OPENQUERY(LINKEDSERVER,
'SELECT * FROM TABLE1 WHERE ID > @ID')

any ideas / links would be appreciated.
regards,
Jamie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-11 : 07:44:50

You have to construct the entire SQL statement as dynamic SQL, then execute it:

DECLARE @ID int, @sql varchar(8000)
SET @ID = 10

SET @SQL='SELECT * FROM OPENQUERY(LINKEDSERVER,
''SELECT * FROM TABLE1 WHERE ID > ' + CAST(@ID as varchar) + ''')'

EXEC(@sql)

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 07:50:22

and can I write this as a sp likie :

create sptest as

DECLARE @ID int, @sql varchar(8000)
SET @ID = 10

SET @SQL='SELECT * FROM OPENQUERY(LINKEDSERVER,
''SELECT * FROM TABLE1 WHERE ID > ' + CAST(@ID as varchar) + ''')'

EXEC(@sql)
GO

then in analyser exec sptest ?

thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-11 : 07:56:24

Yep, that will work to. You can also change it to:

create sptest @ID int as

DECLARE @sql varchar(8000)

SET @SQL='SELECT * FROM OPENQUERY(LINKEDSERVER,
''SELECT * FROM TABLE1 WHERE ID > ' + CAST(@ID as varchar) + ''')'

EXEC(@sql)

And then do this:

EXEC sptest 10

You can then pass any value to the query.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 08:08:47

thats exactly what I am trying to do !
cheers.
however, I think I'm having some problems getting the ' s correct.

so far :

SET @SQL = 'SELECT * FROM OPENQUERY(LS,
''SELECT d.ID
, MAX(CASE WHEN f.Reference = ''Respondent'' THEN d.data ELSE NULL END) ''Respondent''
FROM FIELD_DATA d JOIN field_LIST f
ON d.fieldListID = f.FieldListID
GROUP BY SheetID
HAVING MAX(CASE WHEN f.Reference = ''Respondent'' THEN CONVERT(int,d.data) ELSE NULL END) > @ID ''
)
'

I am getting a syntax error on the first respondent.
do my ' s look ok to you ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-11 : 08:11:37

I don't see any problems. What I recommend is to change the line EXEC(@sql) to SELECT @sql. When you run the procedure it will generate the SQL statement. You can copy and paste this into another QA window and test it. If there are errors they are likely to be more specific.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 08:52:04

cool, that little technique will come in useful !
I thin I've figured it out, I need to put respondent in 8 's instead of 4 so :

,MAX(CASE WHEN f.Reference = ''''Respondent'''' THEN d.data ELSE NULL END) ''''Respondent''''

this now works.
thanks for all the help rob.

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 08:54:27

bugger !
just put all my sp together went to run it and errored :

Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'LS' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.

??!

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 09:03:18

ah ha ! again, I've sorted it.
(having a conversion with my self)
:o)
starts DTC on the linked server and all is well !

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-11 : 09:09:15

Would'nt it be possible to just query the linked server in the following manner and avoid Dynamic SQL?


SELECT * 
FROM <linkedservername>.<dbname>.<objectowner>.<table1>
WHERE ID > @ID

jamie
Aged Yak Warrior

542 Posts

Posted - 2003-12-11 : 09:11:20

never used that before , I have only ever used openquery for linkedservers.
I'll look into it.
thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources