T-SQL 系統函數@ROWCOUNT的使用

在專案程式撰寫中，突然發現有一段預存程式怪怪的，因為他所產生出來的Exection 應該是不會發生的，但是卻出現了。程式是像這樣的

CREATE PROCEDURE UPDATE_TABLE1
	@ParamKey		VARCHAR(10)
	,@ParamValue	VARCHAR(20)
	,@ParamDate		DATETIME
AS
BEGIN
	
	--更新TABLE1
	UPDATE TABLE1
	SET ColumnValue = @ParamValue
	,ColumnDate = @ParamDate
	WHERE ColumnKey = @ParamKey
	
	--產生一串字
	DECLARE @VarText VARCHAR(20)
	SET @VarText = '這是示範資料' + ColumnValue
	
	--前面的執行程序有影響1筆(含)以上的資料
	--再新增資料到TABLE2
	IF @@ROWCOUNT > 0
		INSERT INTO TABLE2 (ColumnKey, ColumnText)
		VALUES (@ParamKey, @VarText)
END

乍看之下好像沒什麼不對，但執行時卻會發生Excetion [INSERT 陳述式與 FOREIGN KEY 條件約束 "FK_TABLE2_ColumnKey" 衝突。衝突發生在資料庫 "DBTest"，資料表 "dbo.TABLE1", column 'ColumnKey'。]，其實這段語法隱藏了一個陷阱…

在TechNet中SQL Server的系統函數中有提到@@ROWCOUNT的使用，有幾個事情要注意：
1. 進行簡單指派的陳述式一律會將 @@ROWCOUNT 值設為 1。  它不會傳送任何資料列給用戶端。 以下是這些陳述式的範例：SET @local_variable、RETURN、READTEXT 以及 select without query 陳述式 (例如，SELECT GETDATE() 或 SELECT 'Generic Text')。
2. DECLARE CURSOR 和 FETCH 會將 @@ROWCOUNT 值設為 1。
3. EXECUTE 陳述式會保留前一個 @@ROWCOUNT。
4. 像 USE、SET <option>、DEALLOCATE CURSOR、CLOSE CURSOR、BEGIN TRANSACTION 或 COMMIT TRANSACTION 等陳述式，會將 ROWCOUNT 值重設為 0。
所以如果要用@@ROWCOUNT去判斷，記得這些規則，否則判斷就會不正確。

因此上面的語法只要修改成

CREATE PROCEDURE UPDATE_TABLE1
	@ParamKey		VARCHAR(10)
	,@ParamValue	VARCHAR(20)
	,@ParamDate		DATETIME
AS
BEGIN
	
	--產生一串字
	DECLARE @VarText VARCHAR(20)
	SET @VarText = '這是示範資料' + ColumnValue
	
	--更新TABLE1
	UPDATE TABLE1
	SET ColumnValue = @ParamValue
	,ColumnDate = @ParamDate
	WHERE ColumnKey = @ParamKey	
	
	--前面的執行程序有影響1筆(含)以上的資料
	--再新增資料到TABLE2
	IF @@ROWCOUNT > 0
		INSERT INTO TABLE2 (ColumnKey, ColumnText)
		VALUES (@ParamKey, @VarText)
END

就解決了

參考資料：
1. TechNet - @@ROWCOUNT (Transact-SQL)：http://technet.microsoft.com/zh-tw/library/ms187316.aspx