ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000),
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0 IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256) SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1 INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId IF (@@ROWCOUNT <> @Num)[color=#0000FF]//判斷@taRoles的記錄在aspnet_Roles是不是都有
BEGIN
SELECT TOP 1 Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END[/color]
DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1 WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1 INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId IF (@@ROWCOUNT <> @Num)
BEGIN
DELETE FROM @tbNames
WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE au.UserId = u.UserId) INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
FROM @tbNames INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users au, @tbNames t
WHERE LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
END IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
BEGIN
SELECT TOP 1 UserName, RoleName
FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
SELECT UserId, RoleId
FROM @tbUsers, @tbRoles IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
這是MS寫的存儲過程 ,紅色部分感覺有些問題
membership項目的
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId IF (@@ROWCOUNT <> @Num)判斷@taRoles的記錄在aspnet_Roles是不是都有 BEGIN//我就覺得這裡多此一舉了,下面應該就是直接rollback事務 返回2
SELECT TOP 1 Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END這裡有問題
如果是这样的话,你的想法是对的,直接回滚
你有沒有看
@TranStarted變量
@TranStarted 變量在啟動事務時 set @TranStarted = 1
牠這裡if(@TranStarted = 1 )肯定是成立的
//下面的肯定會執行,那有什么意義?
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)