How Can I Use Parameters with OPENQUERY in SQL Server?

Using parameters in OPENQUERY

OPENQUERY is a powerful tool for accessing linked server data, but incorporating parameters into queries can be challenging. The documentation clearly states that OPENQUERY does not accept variables for its parameters.

Solution:

To overcome this limitation, several workarounds are available:

• Pass a basic value: For simple cases where you need to pass in a specific value, use code like this:

<code class="language-sql">DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT @VAR = 'CA'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL)</code>

• Pass the entire query: When you need to pass in the complete query or linked server name, use code similar to the following:

<code class="language-sql">DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')'
EXEC (@OPENQUERY+@TSQL)</code>

• Use Sp_executesql stored procedure: To simplify the code and avoid multiple layers of quotes, use the following method:

<code class="language-sql">DECLARE @VAR char(2)
SELECT @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
N'@state char(2)',
@VAR</code>

The above is the detailed content of How Can I Use Parameters with OPENQUERY in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!