SQL Server存储过程事物控制（为一个折腾好久的异常记录）

USE [DBName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROC [dbo].[CreateDept]    
    @Name NVARCHAR(100) = N''
AS
--开启产生运行时错误，整个事务将终止并回滚
SET XACT_ABORT ON 
--SET XACT_ABORT ON  放在AS之后。

	--定义变量
	DECLARE @NewID INT  = 0; --插入后生成的ID

	--验证预算名称是否在该公司下已经存在
	IF EXISTS(SELECT ID FROM dbo.tDepts WHERE [Name] = @Name)
		BEGIN
		    SELECT -1 AS ErrorCode,'名称已经存在' AS ErrorMsg,0 AS [NewID]; --关键字需要加[]进行区分
			RETURN; 
		END
		 
	--事物开启需要在RETURN之后进行。如果事物中包含RETURN,需要在RETURN之前。进行ROLLBACK或者COMMIT操作。
	--这样才能保证事物完整。有开有关。否则事物开启后没有结束操作，会造成锁。
	--注意顺序。本次异常就是没有考虑到下面BEGIN TRY 的位置造成。切记

--添加事务保证下面的行级锁保持到事务的结束
BEGIN TRY
BEGIN TRAN;
	--开始插入
	INSERT INTO dbo.tDepts([Name])VALUES(@Name);
---@@IDENTITY 也可以获取新生成的ID。但是它是全局的，
	--这时候如果出现多个作用域的情况的时候，@@IDENTITY所取得的ID号就是最后一个作用域产生的结果。
	--而 SCOPE_IDENTITY   只返回当前作用域的值
	SET @NewID = SCOPE_IDENTITY(); 
	
	SELECT 0 AS ErrorCode,'创建成功' AS ErrorMsg,@NewID AS [NewID];
		
END TRY
BEGIN CATCH	
	SELECT -1 AS ErrorCode,'创建失败' AS ErrorMsg,0 AS @NewID;

    IF @@TRANCOUNT > 0
		ROLLBACK;

	--异常需要在ROLLBACK之后跑出，否则直接THROW，则事物没有ROLLBACK。同样造成锁
	--也是位置问题，切记

	THROW;
END CATCH;

IF @@TRANCOUNT > 0
	COMMIT;

 

转载于:https://my.oschina.net/niaoge/blog/1648945