allow a funciton to pass through a linkedserver open query in short form code example

Example 1: allow a funciton to pass through a linkedserver open query in short form

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

Example 2: allow a funciton to pass through a linkedserver open query in short form

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)

Example 3: allow a funciton to pass through a linkedserver open query in short form

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)