|
AddUsersToRoles Procedure with your custom COLLATE
|
|
USE
GO
[YordatabaseName]
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles] Script Date: 06/17/2009 13:22:03 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER OFF
GO
CREATE
@ApplicationName
PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]nvarchar(256),
@UserNames
nvarchar(4000),
@RoleNames
nvarchar(4000),
@CurrentTimeUtc
AS
BEGIN
datetime
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationNameIF (@AppId IS NULL)
RETURN(2)
DECLARE @TranStarted bit
SET @TranStarted = 0IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1END
DECLARE @tbNames table(Name nvarchar(256) COLLATE YorCollate _AS 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 = 0SET @Pos = 1WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1END
INSERT INTO @tbRolesSELECT RoleIdFROM dbo.aspnet_Roles ar, @tbNames tWHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppIdIF (@@ROWCOUNT <> @Num)
BEGIN
SELECT TOP 1 Name
FROM @tbNamesWHERE 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
DELETE FROM @tbNames WHERE 1=1SET @Num = 0SET @Pos = 1WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N',', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1END
INSERT INTO @tbUsersSELECT UserIdFROM dbo.aspnet_Users ar, @tbNames tWHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppIdIF (@@ROWCOUNT <> @Num)
BEGIN
DELETE FROM @tbNamesWHERE 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, @CurrentTimeUtcFROM @tbNamesINSERT INTO @tbUsersSELECT UserIdFROM dbo.aspnet_Users au, @tbNames tWHERE LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppIdEND
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, RoleNameFROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles rWHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleIdIF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
SELECT UserId, RoleIdFROM @tbUsers, @tbRolesIF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
|