How to pass a variable to a linked server query

Article translations Article translations
Article ID: 314520 - View products that this article applies to.
This article was previously published under Q314520
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to pass a variable to a linked server query.

When you query a linked server, you frequently perform a pass-through query that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view the examples in SQL Server Books Online to see how to do this by using pre-defined Transact-SQL strings, but there are no examples of how to pass a variable to these functions. This article provides three examples of how to pass a variable to a linked server query.

To pass a variable to one of the pass-through functions, you must build a dynamic query.

Any data that includes quotes needs particular handling. For more information, see the "Using char and varchar Data" topic in SQL Server Books Online and see the following article in the Microsoft Knowledge Base:
156501 INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks

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
				

REFERENCES

For additional information, see the following topics in SQL Server Books Online:
"OPENROWSET"
"OPENQUERY"
"OPENDATASOURCE"
"Using sp_executesql"
"sp_executesql"

Properties

Article ID: 314520 - Last Review: December 22, 2005 - Revision: 4.4
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbhowtomaster KB314520

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com