How Can I Pass Parameters to OPENQUERY in SQL Server?

Parameter Passing in OPENQUERY: A Workaround

SQL Server's OPENQUERY command facilitates querying linked servers. However, directly passing variables as parameters is not supported. This article outlines effective workarounds.

Methods for Parameterized Queries

Several strategies can be used to effectively pass parameters, despite the limitations of OPENQUERY:

1. Hardcoding Basic Values:

For queries with known structures but requiring specific values, dynamic SQL offers a solution:

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

2. Passing the Entire Query:

This approach is ideal for scenarios where the entire T-SQL query or the linked server name (or both) needs to be dynamically determined:

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

3. Leveraging sp_executesql:

To simplify complex quoting, the sp_executesql stored procedure provides a cleaner alternative:

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

By employing these techniques, developers can successfully incorporate parameterized queries with OPENQUERY, overcoming its inherent limitations. Choose the method best suited to your specific needs and query complexity.

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