T-SQL, Parameterized OpenQuery,

openquery_parameterized_example1.sql

-- =============================================
-- 在OpenQuery中使用參數
-- 適用於讀取遠端資料庫時有join狀況下可加速查詢速度。
-- ref → http://www.cnblogs.com/Dannier/archive/2011/09/21/openquery.html
-- =============================================

CREATE PROCEDURE [dbo].[qryRemoteDatabase] 
(
	@CARD_NO VARCHAR(16)
)
AS
BEGIN

--# STEP 1：先寫一遍未以OpenQuery執行的script，以作為參考
--SELECT [CARD_NO] = SubString(A.CARD_NBR, 4, 16)
--, [HOLDER_NAME] = RTrim(B.CHI_NAME)
--, [HOLDER_IDN] = RTrim(A.NID_PrimaryCard)
--, [CREDIT_LIMIT] = C.CREDIT_LIMIT
--FROM linked_servername.RemoteDB.dbo.PrimaryTable A WITH (NOLOCK)
--INNER JOIN linked_servername.RemoteDB.dbo.SecondTable B WITH (NOLOCK) ON A.NID_PrimaryCard = B.NID_PrimaryCard
--INNER JOIN linked_servername.RemoteDB.dbo.MappedTable C WITH (NOLOCK) ON B.CUSTOMER_NBR = C.CUSTOMER_NBR
--WHERE A.CARD_NBR = @CARD_NO;

--# STEP 2：再轉換以OpenQuery可執行的script，注意字串的串接寫法。

DECLARE @SQL1 NVARCHAR(MAX);
DECLARE @SQL2 NVARCHAR(MAX);

SET @SQL1 = 'SELECT [CARD_NO] = SubString(A.CARD_NBR, 4, 16)
,[HOLDER_NAME] = RTrim(B.CHI_NAME)
,[HOLDER_IDN] = RTrim(A.NID_PrimaryCard)
,[CREDIT_LIMIT] = C.CREDIT_LIMIT
FROM RemoteDB.dbo.PrimaryTable A WITH (NOLOCK)
INNER JOIN RemoteDB.dbo.SecondTable B WITH (NOLOCK) ON A.NID_PrimaryCard = B.NID_PrimaryCard
INNER JOIN RemoteDB.dbo.MappedTable C WITH (NOLOCK) ON B.CUSTOMER_NBR = C.CUSTOMER_NBR
WHERE A.CARD_NBR = ''' + @CARD_NO + '''';

SET @SQL1 = REPLACE(@SQL1, '''',''''''); -- 注意
SET @SQL2 = 'SELECT * FROM OPENQUERY(linked_servername,''' + @SQL1 + ''')'; 

--PRINT @SQL2; -- tracing
EXEC(@SQL2);

END

openquery_simple_example.sql

CREATE VIEW [dbo].[vwRemoteDatabase]
AS

SELECT * FROM OPENQUERY(linked_servername, 
  'SELECT A.FormNo ,D.FormName ,B.idCardNumber ,B.homePhone ,B.companyPhone ,B.Mobile 
  FROM RemoteDB.dbo.PrimaryTable AS A WITH (NOLOCK) 
  INNER JOIN RemoteDB.dbo.SecondTable AS B WITH (NOLOCK) ON A.InstanceID = B.InstanceID 
  LEFT OUTER JOIN RemoteDB.dbo.ThirdTable AS D WITH (NOLOCK) ON D.FormID = A.FormID 
  WHERE A.FormNo <> ''''
  AND A.InstanceStatus = ''07''
  AND LEFT(D.FormName,8) in (''AX02-015'',''AX02-005'')
  AND B.C_card_no is not null'
);

GO