TodoSomeThing.sql

出自ProgWiki

用途

• 這是一個MS-SQL預存程序與交易機制的範本

程式碼

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[TodoSomeThing]
(
 
	@id AS INT
)
AS
BEGIN
 
	SET NOCOUNT ON;
 
        --要回傳的錯誤訊息
	DECLARE @RetMsg NVARCHAR(256);
	SET @RetMsg = N'';
 
        --是否有錯誤的判斷依據(以便當有錯時快速跳出原本的SQL操作)
	DECLARE @IsExecuteError INT;
	SET @IsExecuteError =  0;
 
        --開始交易機制
	BEGIN TRANSACTION
 
 
	--一些SQL操作(依此類推)
	IF (@IsExecuteError = 0)
		BEGIN
			UPDATE [TestTable] SET
				[UPDATE]=GETDATE()
				WHERE id=@id;
 
			IF ((@@ERROR <> 0) or (@@ROWCOUNT = 0))
                               BEGIN
					SET @IsExecuteError = @IsExecuteError + 1;
                                        SET @RetMsg = "無法正常Update資料" + CONVERT(NVARCHAR,@id);
                               END
		END
 
 
	IF (@IsExecuteError = 0)
		BEGIN
                        --確認交易
			COMMIT TRANSACTION
			SELECT N'成功!' AS RetMsg
		END
	ELSE
		BEGIN
                        --取消交易
			ROLLBACK TRANSACTION
 
			IF (LEN(@RetMsg)=0)
				SELECT N'失敗!' AS RetMsg
			ELSE
				SELECT @RetMsg AS RetMsg
		END
END