Embed OPENQUERY syntax into stored proc
Posted on 2006-05-23
I've been testing a Linked Server and querying the Linked Server via OPENQUERY with some success up to now, but now I need to embed the OPENQUERY code inside a stored proc ....
This is the code I've been running manually in Query Analyzer (note that this code works fine at this point and LS is the name of my Linked Server) :
<CODE SNIP START>
SET QUOTED_IDENTIFIER Off
declare @InVal char(8)
declare @sql varchar(4000)
set @InVal = '02710185'
set @sql = "SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE
col2 =''" + @InVal + "''')"
<CODE SNIP END>
Inside QA, this works as expected, as @SQL contains :
SELECT * FROM OPENQUERY(LS,'SELECT col1, col2 FROM mytab WHERE
So far, so good.
But now I'd like to put this code inside a stored procedure (eg sp_testLinkedServer) so that the code is actually executed (rather than my above example of PRINT @SQL). Initially, I'd be happy to leave @InVal hardcoded as '02710185' as in the above example, but I'd then like to refine the stoored procedure so that it cam accept an input parameter and place that parameter in @InVal.
Can anyone help with this, please ?