if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[aspnet_Roles_CreateRole]
'
)
and
OBJECTPROPERTY
(id,
N ' IsProcedure ' ) = 1 )
drop procedure [ dbo ] . [ aspnet_Roles_CreateRole ]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- 此存储过程主要是创建用户角色
CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
@ApplicationName nvarchar ( 256 ),
@RoleName nvarchar ( 256 )
AS
BEGIN
-- 声明变量DECLARE
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
-- @@TRANCOUNT:返回当前连接的事务数
IF ( @@TRANCOUNT = 0 )
BEGIN
-- 使 @@TRANCOUNT 递增 1
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
-- 执行存储过程,并返回参数
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName , @ApplicationId OUTPUT
-- 返回最后执行的 Transact-SQL 语句的错误代码,如正确执行返回0
IF ( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = - 1
-- GOTO语句执行跳转
GOTO Cleanup
END
-- 是否已经存在要创建的ID
IF ( EXISTS ( SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER ( @RoleName ) AND ApplicationId =
@ApplicationId ))
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
COMMIT TRANSACTION
END
RETURN ( 0 )
Cleanup:
IF ( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
-- 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
N ' IsProcedure ' ) = 1 )
drop procedure [ dbo ] . [ aspnet_Roles_CreateRole ]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- 此存储过程主要是创建用户角色
CREATE PROCEDURE dbo.aspnet_Roles_CreateRole
@ApplicationName nvarchar ( 256 ),
@RoleName nvarchar ( 256 )
AS
BEGIN
-- 声明变量DECLARE
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
-- @@TRANCOUNT:返回当前连接的事务数
IF ( @@TRANCOUNT = 0 )
BEGIN
-- 使 @@TRANCOUNT 递增 1
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
-- 执行存储过程,并返回参数
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName , @ApplicationId OUTPUT
-- 返回最后执行的 Transact-SQL 语句的错误代码,如正确执行返回0
IF ( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = - 1
-- GOTO语句执行跳转
GOTO Cleanup
END
-- 是否已经存在要创建的ID
IF ( EXISTS ( SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER ( @RoleName ) AND ApplicationId =
@ApplicationId ))
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
COMMIT TRANSACTION
END
RETURN ( 0 )
Cleanup:
IF ( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
-- 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
心得:在写存储过程中,要注意事务的回滚和事务执行的状态。
@@ERROR:是否正确执行
@@TRANCOUNT :当前连接事务数
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT:exec,执行存储过程,output返回的参数
BEGIN TRANSACTION:开始事务
COMMIT TRANSACTION:提交事务
ROLLBACK TRANSACTION:回滚事务