T-SQL Dynamic SQL, sp_executesql, openquery

sp_executesql 範例.sql

-- =====================================================
-- sp_executesql OUTPUT 範例
-- 說明：動態組織SQL Command,可使用EXEC或sp_executesql。
-- 使用 EXEC，ex: EXEC(@sql); 其中 @sql 放動態組織好的指令，但無法OUTPUT。
-- 使用新指令 sp_executesql 則可以設定 OUTPUT 參數。
-- =====================================================

CREATE PROCEDURE [dbo].[prMyProcedure] 
	@sub_system CHAR(2),
	@file_no INT,
	@receive_date VARCHAR(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	DECLARE @sql NVARCHAR(1000);
	DECLARE @upd_cnt INT;

  	--## 原生SQL Script --- 比較用
	---- close TSD_ChargeBack
	--UPDATE TSD_ChargeBack_TW 
	--	SET CLOSE_DATE = Convert(VARCHAR(10),GETDATE(),111) 
	--	WHERE CLOSE_DATE IS NULL 
	--	  AND FILE_NO = @file_no AND RECEIVE_DATE = @receive_date;	
	---- return
	--SELECT @@ROWCOUNT;

  	--## 用字串態動組織SQL Command，其中有OUTPUT argument。此例為 @upd_cnt。
	SET @sql = N'UPDATE TSD_ChargeBack_' + @sub_system 
		 + N' SET CLOSE_DATE = Convert(VARCHAR(10),GETDATE(),111) '		
		 + N'WHERE CLOSE_DATE IS NULL '
		 + N'AND FILE_NO = @file_no AND RECEIVE_DATE = @receive_date; '
		 + N'SET @upd_cnt = @@ROWCOUNT; ' 

	EXEC sp_executesql @sql
		, N'@file_no INT, @receive_date VARCHAR(10), @upd_cnt INT OUTPUT'
		, @file_no, @receive_date, @upd_cnt OUTPUT

	-- RETURN
	SELECT @upd_cnt;

END

sp_executesql_with_openquery.sql

-- =============================================
-- Description:	同時使用sp_executesql與openquery。
-- =============================================
CREATE PROCEDURE [dbo].[prCheckRemoteDataWithParameter]
	 @Account_Number VARCHAR(16) -- parameter
	,@IsMatch BIT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

--# STEP 1：先寫一遍未以OpenQuery執行的script，以作為參考
--SET @IsMatch = 0; -- 先預設失敗 not match
--SELECT @IsMatch = 1 FROM OPENQUERY(linked_servername, 'SELECT TOP 1 * FROM remoteDB.dbo.DataTable WHERE Account_Number = ''0001501000030559'''); -- match or not.
--PRINT @IsMatch;

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

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

-- 換掉此行SQL指令，此例是SQL Server不過對象也可以是Oracle等異質資料庫。
SET @SQL1 = 'SELECT TOP 1 * FROM remoteDB.dbo.DataTable WHERE Account_Number = ''' + @Account_Number + '''';
--PRINT @SQL1;

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

SET @IsMatch = 0; -- 先預設失敗 not match
EXEC SP_EXECUTESQL @SQL2
	, N'@IsMatch BIT OUTPUT'
	, @IsMatch OUTPUT

END

GO

transaction.txt

--
-- transaction 用法
-- ref→[TRANSACTION ISOLATION LEVEL](https://ithelp.ithome.com.tw/articles/10194749)
-- 
-- TRANSACTION ISOLATION LEVEL 大致上可以分成四種：
--
-- READ UNCOMMITTED
-- 寬鬆級別：A 交易更新但未確認資料，B交易不能更新只能讀取(直到A交易提交後)，確保交易更新資料不會有問題。
-- 
-- READ COMMITTED
-- 比較嚴格一些：A 交易更新並確認資料前，其他交易不能讀取該資料
-- 官網說明：READ COMMITTED 隨著 READ_COMMITTED_SNAPSHOT 設定而異，請參考最下方餐可考資料
--
-- REPEATABLE READ
-- 更嚴格一點的限制：讀取中資料會被鎖定，確保同一筆交易中的讀取資料必須相同
-- 
-- SERIALIZABLE
-- 最嚴謹的限制：A 交易讀取時，B交易更新要排隊；A交易更新時，B交易讀取與更新都需要排隊
--

CREATE PROCEDURE [dbo].[sp_GetTsdCBInfoSN]
	@param1 <data_type>,
	@param2 <data_type>
AS

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION

  -- update/insert/delete statements
  update ...
  insert ...
  delete ...

COMMIT