How Can I Use Parameters with SQL Server's OPENQUERY?

SQL Server OPENQUERY with Parameters: Workarounds

SQL Server's OPENQUERY executes queries on linked servers, but directly using variables within its arguments isn't supported. This necessitates workarounds for parameter passing.

Method 1: Passing Simple Values via String Concatenation

For straightforward queries needing specific values, dynamic SQL with string concatenation is a viable 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>

Method 2: Passing the Entire Query as a String

This approach allows for passing the complete query or even the linked server name dynamically:

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

Method 3: Using sp_executesql for Cleaner Parameter Handling

To avoid complex nested quoting, utilize sp_executesql:

<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;
```  This method offers improved readability and reduces the risk of SQL injection vulnerabilities compared to string concatenation.  Note that you're executing `sp_executesql` on the linked server itself.


Choose the method that best suits your query complexity and preference for code clarity.  Remember to prioritize security and avoid SQL injection vulnerabilities when using dynamic SQL.</code>

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