从临时表中删除记录时将被删除的所有记录

Question

我有一个存储过程，我将一个文档ID字符串传递给它，并且它应该删除其中的roleID = @roleID的documentID，但相反它会删除所有记录根据该角色ID和所有我想要做的就是基础上删除该角色ID从表中documentIDs

我的SP是

ALTER PROCEDURE sp_RemoveDocumentIDsFromRole 
(
@roleID int, 
@group_name varchar(50), 
@DocumentIDString varchar(500), 
@group_display_name varchar(50) 
) 
as 
UPDATE [master_groups] 
set group_name = @group_name, @group_display_name = @group_display_name 
where roleID = @roleID 

-- Creating Variables 
DECLARE @numberLength int 
DECLARE @numberCount int 
DECLARE @TheDocumentIDs VarChar(500) 

DECLARE @sTemp VarChar(100) -- to hold single characters 

    -- Creating a temp table 
DECLARE @T TABLE 
(
    TheDocumentIDs VarChar(500) 
) 
--Initializing Variables for counting 
SET @numberLength = LEN (@DocumentIDString) 
SET @numberCount = 1 
SET @TheDocumentIDs = '' 

--Start looping through the keyword ids 
WHILE (@numberCount <= @numberLength) 

BEGIN 
SET @sTemp = SUBSTRING (@DocumentIDString, @numberCount, 1) 
    IF (@sTemp = ',') 
     BEGIN 
      INSERT @T(TheDocumentIDs) VALUES (@TheDocumentIDs) 
      SET @TheDocumentIDs = '' 
     END 
    IF (@sTemp <> ',') 
     BEGIN 
      SET @TheDocumentIDs = @TheDocumentIDs + @sTemp 
     END 
     SET @numberCount = @numberCount + 1 
END 

declare @rLevel int 
set @rLevel = 0 

delete from [master_group_document_relations] where exists(select documentID = @TheDocumentIDs from @T) and roleID = @roleID 

UPDATE：样本数据

Answer 1

不知道你在@TheDocumentIDs，但我想它应该为你工作。

所有提到@克里斯首先，你应该检查放置条件where子句和documentIds是ID的名单应该与用于条件，而不是平等的，这就是为什么你需要或使用sp_executesql的或填写id到临时表。

  EXECUTE sp_executesql 
      N'delete from [master_group_document_relations] 
      where documentID IN (@TheDocumentIDs) 
       and roleID = @roleID', 
      N'@TheDocumentIDs varchar(500), @roleID int', 
      @DocumentIDString, 
      @roleID; 

，或者如果你想删除的master_group_document_relations记录，其中其documentID是在@t和roleIDTheDocumentIDs试试这个

  delete from [master_group_document_relations] 
      where documentID IN (SELECT TheDocumentIDs FROM @T) 
       and roleID = @roleID 

Answer 2

等于@roleID那就试试这个：

DELETE T1 
FROM [master_group_document_relations] AS T1 
WHERE EXISTS(SELECT 1 
       FROM @T AS T2 
       WHERE T1.documentID = T2.TheDocumentIDs) 
     AND roleID = @roleID