May 28, 2010 at 5:13 pm
Assumptions:
1) 'DIM_User_Approved' is my custom table that I hold all my members details ( rather than using aspnetdb profile setup)
2) The above table is linked to dbo.aspnet_Membership via the GUID UserID
I am building an auto expiry code to run thru my custom 'DIM_User_Approved' table reading expiry date, and if date has passed change the role from whatever it is to the new role called 'Expired'
The logic to do this is when a member has expired
1) remove all roles
2) add new Expired role
The aspnetdb exec I need to run are in order
1) "REMOVED ALL ROLES" : aspnet_UsersInRoles_RemoveUsersFromRoles @ApplicationName, @UserNames, @RolesNames
2) "ADD USER TO EXPIRY ROLE": aspnet_UsersInRoles_AddUsersToRoles @ApplicationName, @UserNames, @RolesNames, @CurrentTimeUtc
I have this beast of code that returns
DECLARE @ExpireDate DATETIME
SET @ExpireDate = '06/25/2010'
SELECT
F.ApplicationName,
E.UserID,
E.Subs_End,
[dbo].[fnUserRolesCSV](A.UserID) AS RolesList
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_Users AS B WITH (NOLOCK) ON A.UserId = B.UserId
INNER JOIN dbo.aspnet_UsersInRoles AS C WITH (NOLOCK) ON B.UserId = C.UserId
INNER JOIN dbo.aspnet_Roles AS D WITH (NOLOCK) ON C.RoleId = D.RoleId
INNER JOIN dbo.DIM_User_Approved AS E WITH (NOLOCK) ON B.UserId = E.UserGUID
INNER JOIN dbo.aspnet_Applications AS F WITH (NOLOCK) ON A.ApplicationId = F.ApplicationId
WHERE D.RoleName IN (SELECT LoweredRoleName FROM dbo.aspnet_Roles WITH (NOLOCK)
WHERE AllowExpire =1)
GROUP BY F.ApplicationName,A.UserId,E.UserID,E.Subs_End
HAVING E.Subs_End < @ExpireDate
-- FUNCTION IN THE ABOVE SELECT
ALTER FUNCTION [dbo].[fnUserRolesCSV] (
@UserID UNIQUEIDENTIFIER)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @res VARCHAR(500)
SET @res = (SELECT STUFF((SELECT ',' + C.RoleName
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_UsersInRoles AS B WITH (NOLOCK) ON A.UserID = B.UserID
INNER JOIN dbo.aspnet_Roles AS C WITH (NOLOCK) ON B.RoleId = C.RoleId
WHERE A.UserID = @UserID
FOR XML PATH('')),1, 1, ''))
RETURN (@res)
END
This is the output of the above that I need to run the above logic thru, see image named OUTPUT.jpg
Question : What do I do to run the above aspnetdb exec on each line of the output, A While loop, Cursor, SELECT ???
Please advise, thanks
May 28, 2010 at 7:25 pm
Question : What do I do to run the above aspnetdb exec on each line of the output, A While loop, Cursor, SELECT ???
A cursor is bad. A while loop can be worse. Use a good set-based approach.... this sounds like a good use for CROSS APPLY. See the link "Using APPLY" in my signature.
I'd also recommend changing your scalar function to an in-line table-valued function. It can dramatically improve your performance. It would be read easy to change:
ALTER FUNCTION [dbo].[fnUserRolesCSV] (
@UserID UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN (SELECT STUFF((SELECT ',' + C.RoleName
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_UsersInRoles AS B WITH (NOLOCK) ON A.UserID = B.UserID
INNER JOIN dbo.aspnet_Roles AS C WITH (NOLOCK) ON B.RoleId = C.RoleId
WHERE A.UserID = @UserID
-- you might want to consider putting an ORDER BY here to control the order of the rolenames!
FOR XML PATH('')),1, 1, ''))
then just move it from the select's column list to the from clause.
Edit: almost forgot... all those (NOLOCK) hints can make the query optimizer pick bad plans... it's usually best to get rid of those and just let the query optimizer do it's job - almost always it will be better. You should only use hints when you have a good, documented reason, after lots of testing, for each one.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 28, 2010 at 7:44 pm
Thanks for viewing..
BUT how do I take the OUTPUT ( data in the jpeg) by row and execute the two aspnetdb stored proceedures for each row.
How does Cross Apply work ???
I mean I was thinking like this...
Do while n < RecordCount
EXEC <stored proc1> <row data n>
EXEC <stored proc2> <row data n>
n +1
loop
Sorry I need code examples, thanks
May 28, 2010 at 9:52 pm
I be going with logic like this,
- It only runs once a day via a JOB
- Let me know if you can make run faster
DECLARE @CurrTime DATETIME
DECLARE @ExpireDate DATETIME
DECLARE @RowCnt INT
DECLARE @MaxRows INT
DECLARE @temp TABLE (rowID INT IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
AppName NVARCHAR(256) NOT NULL,
UserName NVARCHAR(256) NOT NULL,
Subs_End DATETIME NOT NULL,
Roles_List NVARCHAR(500) NOT NULL)
SET @RowCnt = 1
SET @ExpireDate = '06/25/2010'
SET @CurrTime = GETUTCDATE()
BEGIN
WITH MembersToExpire AS(SELECT
F.ApplicationName AS 'AppName',
B.LoweredUserName AS 'UserName',
E.Subs_End,
[dbo].[fnUserRolesCSV](A.UserID) AS 'RolesList'
FROM dbo.aspnet_Membership AS A WITH (NOLOCK)
INNER JOIN dbo.aspnet_Users AS B WITH (NOLOCK) ON A.UserId = B.UserId
INNER JOIN dbo.aspnet_UsersInRoles AS C WITH (NOLOCK) ON B.UserId = C.UserId
INNER JOIN dbo.aspnet_Roles AS D WITH (NOLOCK) ON C.RoleId = D.RoleId
INNER JOIN dbo.DIM_User_Approved AS E WITH (NOLOCK) ON B.UserId = E.UserGUID
INNER JOIN dbo.aspnet_Applications AS F WITH (NOLOCK) ON A.ApplicationId = F.ApplicationId
WHERE D.RoleName IN (SELECT LoweredRoleName FROM dbo.aspnet_Roles WITH (NOLOCK)
WHERE AllowExpire =1)
GROUP BY A.UserID, F.ApplicationName,B.LoweredUserName,E.Subs_End
HAVING E.Subs_End < @ExpireDate)
INSERT INTO @temp
SELECT AppName,UserName,Subs_End,RolesList
FROM MembersToExpire
END
SET @MaxRows = (SELECT COUNT(*) FROM @temp)
IF @MaxRows > 0
BEGIN
DECLARE @AppName NVARCHAR(256)
DECLARE @UserName NVARCHAR(256)
DECLARE @Roles_List NVARCHAR(500)
DECLARE @Run1 INT
DECLARE @Run2 INT
DECLARE @procRet INT
WHILE @RowCnt <= @MaxRows
BEGIN
SELECT@AppName = AppName
,@UserName = UserName
,@Roles_List = Roles_List
FROM @temp
WHERE rowID = @RowCnt
-- TEST
-- SELECT @AppName + ' ' + @UserName + ' ' +@Roles_List
EXEC @Run1 = aspnet_UsersInRoles_RemoveUsersFromRoles @AppName,@UserName,@Roles_List
IF @Run1 >=0
EXEC @Run2 = aspnet_UsersInRoles_AddUsersToRoles @AppName, @UserName, 'Expired', @CurrTime
-- TEST
--SELECT @Run1 AS 'Run1', @Run2 AS 'Run2', @UserName AS 'UserName'
SET @RowCnt = @RowCnt + 1
END
END
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply