ALTER
PROCEDURE
[
dbo
]
.aspnet_Users_DeleteUser
--
删除用户信息
@ApplicationName NVARCHAR ( 256 ),
@UserName NVARCHAR ( 256 ),
@TablesToDeleteFrom INT ,
@NumTablesDeletedFrom INT OUTPUT
AS
/**/ /*因为用户的信息分开保存在多张表中,所以在删除某用户信息的
时候同时要在多张表中删除该用户的信息.所以下面才进行多个表的判断和操作.
其中@TablesToDeleteFrom会接受由aspnet_Profile_DeleteProfiles传来的
4,这个数用于对以下的于运算.这个存储过程还可能接受其他存储过程传过来的参数,不
过目前我就看到这个. */
BEGIN
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF ( @@TRANCOUNT = 0 ) -- 如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode INT -- -声明错误代码
DECLARE @RowCount INT -- -声明行数
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER ( @UserName )
AND u.ApplicationId = a.ApplicationId
AND LOWER ( @ApplicationName ) = a.LoweredApplicationName
/**/ /*从aspnet_Users和aspnet_Applications表中查询输入的@UserName的UserId并
付给声明的@UserId*/
IF ( @UserId IS NULL ) -- 如果此用户不存在,跳转到回滚
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF (( @TablesToDeleteFrom & 1 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_Membership ' ) AND (type = ' U ' ))))
/**/ /* &(按位 AND)在两个整型值之间执行按位逻辑与运算。
后面是判断是否存在aspnet_Membership表,直接运行后面这条语句的结果是aspnet_Membership*/
/**/ /*即输入参数@TablesToDeleteFrom不为0且存在aspnet_Membership表的话,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
-- -删除此用户的所有在aspnet_Membership表中的数据
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 如果有错误则跳转到回滚
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 如果受影响行数不为0,即操作影响了数据行,表示操作成功
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 -- 那个参数的值递增1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF (( @TablesToDeleteFrom & 2 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_UsersInRoles ' ) AND (type = ' U ' ))) )
/**/ /*如果@TablesToDeleteFrom不为0且存在aspnet_UsersInRoles,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
/**/ /*从aspnet_UsersInRoles表删除此用户的所有数据信息*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 如果有错则回滚
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 如果操作成功为@NumTablesDeletedFrom递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF (( @TablesToDeleteFrom & 4 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_Profile ' ) AND (type = ' U ' ))) )
/**/ /*如果@TablesToDeleteFrom不为0且存在aspnet_Profile,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
/**/ /*从aspnet_Profile删除此用户的所有数据*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 有错误则回滚
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 成功则为@NumTablesDeletedFrom递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF (( @TablesToDeleteFrom & 8 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_PersonalizationPerUser ' ) AND (type = ' U ' ))) )
/**/ /*如果@TablesToDeleteFrom不为0且存在aspnet_PersonalizationPerUser,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
/**/ /*从aspnet_PersonalizationPerUser表删除此用户的所有数据信息*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 有错误则跳转到回滚段
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 成功则为@NumTablesDeletedFrom的值递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF (( @TablesToDeleteFrom & 1 ) <> 0 AND
( @TablesToDeleteFrom & 2 ) <> 0 AND
( @TablesToDeleteFrom & 4 ) <> 0 AND
( @TablesToDeleteFrom & 8 ) <> 0 AND
( EXISTS ( SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
/**/ /*如果全部按位于都不为0且aspnet_Users表中是否存在此用户
其实1,2,4,8和1按位于都不为0*/
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
/**/ /*删除aspnet_Users中此用户的数据*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 有错误则跳转到回滚段
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 成功则为@NumTablesDeletedFrom值递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF ( @TranStarted = 1 ) -- 如果事务正常开始到这里
BEGIN
SET @TranStarted = 0 -- 重新设置为0
COMMIT TRANSACTION -- 结束事务
END
RETURN 0
Cleanup: -- -这就是传说中的跳转部分
SET @NumTablesDeletedFrom = 0
IF ( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
@ApplicationName NVARCHAR ( 256 ),
@UserName NVARCHAR ( 256 ),
@TablesToDeleteFrom INT ,
@NumTablesDeletedFrom INT OUTPUT
AS
/**/ /*因为用户的信息分开保存在多张表中,所以在删除某用户信息的
时候同时要在多张表中删除该用户的信息.所以下面才进行多个表的判断和操作.
其中@TablesToDeleteFrom会接受由aspnet_Profile_DeleteProfiles传来的
4,这个数用于对以下的于运算.这个存储过程还可能接受其他存储过程传过来的参数,不
过目前我就看到这个. */
BEGIN
DECLARE @UserId UNIQUEIDENTIFIER
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF ( @@TRANCOUNT = 0 ) -- 如果当前活动事务为0,开始事务并设置事务参数为1
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode INT -- -声明错误代码
DECLARE @RowCount INT -- -声明行数
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER ( @UserName )
AND u.ApplicationId = a.ApplicationId
AND LOWER ( @ApplicationName ) = a.LoweredApplicationName
/**/ /*从aspnet_Users和aspnet_Applications表中查询输入的@UserName的UserId并
付给声明的@UserId*/
IF ( @UserId IS NULL ) -- 如果此用户不存在,跳转到回滚
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF (( @TablesToDeleteFrom & 1 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_Membership ' ) AND (type = ' U ' ))))
/**/ /* &(按位 AND)在两个整型值之间执行按位逻辑与运算。
后面是判断是否存在aspnet_Membership表,直接运行后面这条语句的结果是aspnet_Membership*/
/**/ /*即输入参数@TablesToDeleteFrom不为0且存在aspnet_Membership表的话,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
-- -删除此用户的所有在aspnet_Membership表中的数据
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 如果有错误则跳转到回滚
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 如果受影响行数不为0,即操作影响了数据行,表示操作成功
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 -- 那个参数的值递增1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF (( @TablesToDeleteFrom & 2 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_UsersInRoles ' ) AND (type = ' U ' ))) )
/**/ /*如果@TablesToDeleteFrom不为0且存在aspnet_UsersInRoles,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
/**/ /*从aspnet_UsersInRoles表删除此用户的所有数据信息*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 如果有错则回滚
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 如果操作成功为@NumTablesDeletedFrom递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF (( @TablesToDeleteFrom & 4 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_Profile ' ) AND (type = ' U ' ))) )
/**/ /*如果@TablesToDeleteFrom不为0且存在aspnet_Profile,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
/**/ /*从aspnet_Profile删除此用户的所有数据*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 有错误则回滚
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 成功则为@NumTablesDeletedFrom递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF (( @TablesToDeleteFrom & 8 ) <> 0 AND
( EXISTS ( SELECT name FROM sysobjects WHERE (name = N ' aspnet_PersonalizationPerUser ' ) AND (type = ' U ' ))) )
/**/ /*如果@TablesToDeleteFrom不为0且存在aspnet_PersonalizationPerUser,执行以下*/
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
/**/ /*从aspnet_PersonalizationPerUser表删除此用户的所有数据信息*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 有错误则跳转到回滚段
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 成功则为@NumTablesDeletedFrom的值递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF (( @TablesToDeleteFrom & 1 ) <> 0 AND
( @TablesToDeleteFrom & 2 ) <> 0 AND
( @TablesToDeleteFrom & 4 ) <> 0 AND
( @TablesToDeleteFrom & 8 ) <> 0 AND
( EXISTS ( SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
/**/ /*如果全部按位于都不为0且aspnet_Users表中是否存在此用户
其实1,2,4,8和1按位于都不为0*/
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
/**/ /*删除aspnet_Users中此用户的数据*/
SELECT @ErrorCode = @@ERROR ,
@RowCount = @@ROWCOUNT
IF ( @ErrorCode <> 0 ) -- 有错误则跳转到回滚段
GOTO Cleanup
IF ( @RowCount <> 0 ) -- 成功则为@NumTablesDeletedFrom值递增1
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF ( @TranStarted = 1 ) -- 如果事务正常开始到这里
BEGIN
SET @TranStarted = 0 -- 重新设置为0
COMMIT TRANSACTION -- 结束事务
END
RETURN 0
Cleanup: -- -这就是传说中的跳转部分
SET @NumTablesDeletedFrom = 0
IF ( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END