You are currently offline, waiting for your internet to reconnect

How to pass a variable to a linked server query

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q314520
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
back to the top

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)				
back to the top

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) 				
back to the top

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				
back to the top
REFERENCES
For additional information, see the following topics in SQL Server Books Online:
"OPENROWSET"
"OPENQUERY"
"OPENDATASOURCE"
"Using sp_executesql"
"sp_executesql"
back to the top
distributed query linked server variable parameter dynamic build
Properties

Article ID: 314520 - Last Review: 12/22/2005 23:29:11 - Revision: 4.4

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

  • kbhowtomaster KB314520
Feedback
lementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> "col-sm-6 col-xs-24 ng-scope"> Uruguay - Español
대한민국 - 한국어
España - Español
Paraguay - Español
Venezuela - Español
://c1.microsoft.com/c.gif?DI=4050&did=1&t="> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" d')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >