ALTER PROCEDURE dbo.aspnet_Roles_CreateRole --创建角色
@ApplicationName NVARCHAR(256),
@RoleName NVARCHAR(256)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) --如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
/*根据输入参数执行存储过程返回@ApplicationId,目的应该就是这个*/
IF( @@ERROR <> 0 )--如果有错误则跳转到回滚段
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
/*如果存在此角色返回错误代码1,并跳转到回滚段*/
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
INSERT INTO dbo.aspnet_Roles
(ApplicationId, RoleName, LoweredRoleName)
VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
--不存在就插入此记录
IF( @@ERROR <> 0 ) ---有错误就回滚(还是跳转)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 ) --如果事物执行到这里,且事务参数没变
BEGIN
SET @TranStarted = 0 --重新设置事务参数为0
COMMIT TRANSACTION --结束事务
END
RETURN(0)
Cleanup: ---跳转段
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
@ApplicationName NVARCHAR(256),
@RoleName NVARCHAR(256)
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) --如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
/*根据输入参数执行存储过程返回@ApplicationId,目的应该就是这个*/
IF( @@ERROR <> 0 )--如果有错误则跳转到回滚段
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
/*如果存在此角色返回错误代码1,并跳转到回滚段*/
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
INSERT INTO dbo.aspnet_Roles
(ApplicationId, RoleName, LoweredRoleName)
VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))
--不存在就插入此记录
IF( @@ERROR <> 0 ) ---有错误就回滚(还是跳转)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 ) --如果事物执行到这里,且事务参数没变
BEGIN
SET @TranStarted = 0 --重新设置事务参数为0
COMMIT TRANSACTION --结束事务
END
RETURN(0)
Cleanup: ---跳转段
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END