Call procedure for each value in a column?

I have a a procedure to remove a user from my database:

CREATE PROCEDURE [dbo].[sp_removeUser]
     @UserId        uniqueidentifier    
    ,@result        int OUT
AS
BEGIN  
 SET NOCOUNT ON     
    SET @Result=0

    -- whether User exists 
    IF NOT EXISTS(SELECT 1 FROM [dbo].[Users] (nolock) WHERE UserId = @UserId)  
        RETURN  

    -- Prevent to remove admin
    IF EXISTS(SELECT 1 FROM [dbo].[aspnet_Users] (nolock) WHERE UserId= @UserId AND UserName = 'admin' )
        RETURN


    BEGIN TRAN

    -- Remove User Addresses
    DELETE FROM [dbo].[AddressesUserAccess] WHERE AddressId IN (SELECT AddressId FROM Addresses WHERE UserId = @UserId)
    DELETE FROM [dbo].[AddressesInGroups] WHERE AddressId IN (SELECT AddressId FROM Addresses WHERE UserId = @UserId)
    DELETE FROM [dbo].[Addresses] WHERE UserId = @UserId

    -- Remove User Numbers  
    DELETE FROM [dbo].[NumbersUserAccess] WHERE NumberId IN (SELECT NumberId FROM Numbers WHERE UserId = @UserId)
    DELETE FROM [dbo].[NumbersInGroups] WHERE NumberId IN (SELECT NumberId FROM Numbers WHERE UserId = @UserId)
    DELETE FROM [dbo].[Numbers] WHERE UserId = @UserId

...
... and lots more below...
...

I want to remove a whole bunch of users, so my first thought was to get a column of all the UserIDs I want to remove, then iterate over that, removing each user in turn.

However, after reading some of the other questions on SO asking how to do this, I understand that this is not the prefered way to do it, and instead I should look for a more se开发者_开发技巧t-based solution.

So what is a better way of implenting this procedure to work in a more set-based way?

You could use a split function, e.g.

CREATE FUNCTION dbo.SplitGUIDs
(
   @List       VARCHAR(MAX)
)
RETURNS TABLE
AS
   RETURN 
   (
       SELECT Item = CONVERT(UNIQUEIDENTIFIER, Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'VARCHAR(36)')
           FROM
           (
               SELECT [XML] = CONVERT(XML, '<i>' 
                    + REPLACE(@List, ',', '</i><i>') 
                    + '</i>').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );
GO

Then your procedure would only have to change slightly (though I strongly recommend against the sp_ prefix):

CREATE PROCEDURE dbo.RemoveUsers
    @UserList VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @u TABLE(UserID UNIQUEIDENTIFIER PRIMARY KEY);

    INSERT @u
        SELECT DISTINCT i.Item 
        FROM dbo.SplitGUIDs(@UserList) AS i
        INNER JOIN dbo.Users AS u
        ON u.UserID = i.Item
        WHERE NOT EXISTS 
        (
            SELECT 1 FROM dbo.aspnet_users
            WHERE UserID = i.Item
            AND Username = 'Admin'
        );

    -- now all your delete statements can use IN instead of =
    DELETE ... WHERE UserID IN (SELECT UserID FROM @u);
END
GO

You may have to build the comma-separated list of GUIDs from the application layer or elsewhere, but you can call the procedure like this:

EXEC dbo.RemoveUsers 
@UserList = '89E31B4B-68B5-4B7B-B226-F51BE388F815,C947AE20-DEC1-4EBC-A838-CCCE033BD1FF'

Just note that it will bomb if any of the elements are not a valid GUID.