调用列中每个值的过程？

Question

我有AA过程，从我的数据库中删除用户：

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... 
... 

我要删除一大堆的用户，所以我首先想到的是让所有我想删除的用户ID的一列，然后迭代，依次删除每个用户。

然而，在阅读了一些关于SO的其他问题后，我知道这不是最好的方式，而应该寻找更多基于集合的解决方案。

那么什么是更好的方式来实现这个过程以更基于集合的方式工作？

Answer 1

您可以使用分割功能，例如

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 

那么你的程序将只需要稍微改变（虽然我强烈建议反对以sp_前缀）：

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 

您可能需要从应用层构建的GUID的逗号分隔列表或其他地方，但你可以调用的程序是这样的：

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

只要注意，它会弹，如果任何元素是不是有效的GUID。