including parameters in OPENQUERY

by

From the OPENQUERY documentation it states that:

OPENQUERY does not accept variables
for its arguments.

See this article for a workaround.

UPDATE:

As suggested, I’m including the recommendations from the article below.

Pass Basic Values

When the basic Transact-SQL statement is known, but you have to pass in one or more specific values, use code that is similar to the following sample: 

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)

Pass the Whole Query

When you have to pass in the whole Transact-SQL query or the name of the linked server (or both), use code that is similar to the following sample:

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)

Use the Sp_executesql Stored Procedure

To avoid the multi-layered quotes, use code that is similar to the following sample:

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

More Related Contents:

  1. Passing multiple values for a single parameter in Reporting Services
  2. Parameterize an SQL IN clause
  3. How to set table name in dynamic SQL query?
  4. Pass table as parameter into sql server UDF
  5. How to use parameters “@” in an SQL command in VB
  6. Can we pass parameters to a view in SQL?
  7. “Order By” using a parameter for the column name
  8. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  9. sql query if parameter is null select all
  10. Check if a parameter is null or empty in a stored procedure
  11. Parameterise table name in .NET/SQL?
  12. Call stored procedure with table-valued parameter from java
  13. Merging two tables in SQL [closed]
  14. Syntax combine two stored procedure into one [closed]
  15. How to delete duplicate rows in SQL Server?
  16. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  17. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  18. Limit on the WHERE col IN (…) condition
  19. SQLite – How do you join tables from different databases?
  20. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  21. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  22. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  23. SQL Select Upcoming Birthdays
  24. MySQL, Get users rank
  25. SQL error “ORA-01722: invalid number”
  26. How to use multiple CTEs in a single SQL query?
  27. Why are foreign keys more used in theory than in practice?
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. Challenge,how to implement an algorithm for six degree of separation?
  30. UPSERT *not* INSERT or REPLACE

Leave a Comment