I have a stored procedure that I am passing a string of documentIDs to and its supposed to delete the documentID where its roleID = @roleID, but instead it is deleting all the records based on the roleID and all I want to do is delete the documentIDs from the table based on the roleID
My sp is
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: SAMPLE DATA
Not sure what you get in @TheDocumentIDs, but I suppose it should work for you.
First of all as @Chris mentioned, you should check put condition to where clause and as documentIds is a list of id's it should used with IN condition and not equality, that's why you need or use sp_executesql or fill id's to a temp table.
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;
Or try this
delete from [master_group_document_relations]
where documentID IN (SELECT TheDocumentIDs FROM @T)
and roleID = @roleID
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments