Microsoft KB Archive/314520

= How to pass a variable to a linked server query =

Article ID: 314520

Article Last Modified on 12/22/2005

-

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

-



This article was previously published under Q314520



IN THIS TASK
SUMMARY
 * Pass Basic Values
 * Pass the Whole Query
 * Use the Sp_executesql Stored Procedure

REFERENCES



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 &quot;Using char and varchar Data&quot; 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

