Delete multiple records from different tables with SQL stored procedure

Question

I'm trying to create a SQL agent job that executes a stored procedure every 5 minutes or so, but I'm at a loss with part of the SQL syntax. I'm using forms authentication and I need to select all users that have not been active in the last 15 minutes and have also been marked temporary. To do that, I will be grabbing LastActivityDate from table aspnet_Users and IsTemp from table UserProfile. After selection, I need to delete all traces of this user from my data. Below is the basic structure of my tables necessary for my question:

    aspnet_Users(uniqueidentifier UserId PK)
    aspnet_Membership(uniqueidentifier UserId FK)
    UserProfile(int UserProfileID PK, uniqueidentifier aspnetUserID FK, int IsTemp)
    UserFriend(int UserFriendID PK, int UserProfileID FK)
    Image(int ImageID PK, int UserProfileID FK)
    Message(int MessageID, int UserProfileID FK)

Here's what I have so far:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[CleanUpUserProfiles] 
        -- Add the parameters for the stored procedure here
    AS
    BEGIN
        SET NOCOUNT ON;

        SELECT *
        FROM [UserProfile]
        WHERE IsTemp = 1

        SELECT *
        FROM [aspnet_Users]
        WHERE LastActivityDate < DATEADD(mi, -15, CURRENT_TIMESTAMP)

        SELECT *
        FROM [UserProfile]
        WHERE IsTemp = 1

        DELETE FROM [Message]
        WHERE ??? = @???

        DELETE FROM [Image]

        DELETE FROM [UserFriend]

        DELETE FROM [UserProfile]

        DELETE FROM [aspnet_Membership]

        DELETE FROM [aspnet_Users]

    END
    GO

I know I need a variable to hold the selected users from aspnet_Users in order to know which to delete, but I've no idea how to do that. Secondly, I only need to select users with IsTemp = 1 from UserProfile. How would I include that in my select statement? I also understand that I'll need two SELECT statments. One to select UserID for use in deletion of UserProfile, UserFriend, Image, and Message, and another for selection of the uniqueidentifier I'll be using in aspnet_Users and aspnet_Membership. Please keep in mind I've not dealt with SQL much beyond basic stored procedure creation. Any help is very much appreciated.

If anybody's curious as to why I want to do this... I'm allowing users the option to remain anonymous by creating only a username upon entry to my service. These users will be temporary, and if somebody comes along and wants to use the same username days later, I don't want to prohibit them from doing so. I also don't want to hang on to unnecessary data, feels dirty.

Answer 1

Try this.. First fetch the UserID's that you have to delete and store them in a Temp table.

Select a.UserID into #UserToDelete from aspnet_users a
inner join UserProfile b on a.UserID = b.aspnetUserID
where IsTemp=1 and LastActivityDate < DATEADD(mi, -15, CURRENT_TIMESTAMP)

Now delete all these users from all your transaction tables.

DELETE FROM [Message] where UserProfileID in (select userid from #UserToDelete)
DELETE FROM [Image] where UserProfileID in (select userid from #UserToDelete)   
DELETE FROM [UserFriend] where UserProfileID in (select userid from #UserToDelete)  
DELETE FROM [UserProfile] where aspnetUserID in (select userid from #UserToDelete)
DELETE FROM [aspnet_Membership] where userID in (select userid from #UserToDelete)
DELETE FROM [aspnet_Users] where userID in (select userid from #UserToDelete)