All records being deleted when deleting records from a temp table

Chris

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 Sample Data

hgulyan

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Finding duplicate records from table and deleting all but one with latest date

From Dev

Deleting all table records from core data database using relationship

From Dev

Finding duplicate records from table and deleting all but one with latest date

From Dev

deleting records from table using SQLAlchemy

From Dev

deleting records from a table where there is an inner join

From Dev

Deleting billions of records from Oracle Table containing trillions of records

From Dev

Deleting billions of records from Oracle Table containing trillions of records

From Dev

SQL Loop not adding all input records to temp table

From Dev

Error from Entity Framework when deleting records from table with foreign key

From Dev

records from database being loaded when they are not called

From Dev

SQL Server perform a delete on all child records when deleting from a parent

From Dev

Retriving all records from left table and matching records from right

From Dev

Paste 10000 records from excel sheet into a temp table

From Dev

create temp table from join contained + 65 milion of records Mysql

From Dev

Deleting all records apart from one, and only with a field value in 'type'

From Dev

Preventing users from deleting records in one table that have related records in another table

From Dev

Deleting checked records from table in asp.net

From Dev

Keep all records from one table when using UNION ALL with other table

From Dev

Not getting all records from Table A in left join

From Dev

Get all records from table - EclipseLink

From Dev

Get all records from azure table storage

From Dev

Getting all records from database to Vaadin Table

From Dev

Get all records from another table with pivot

From Dev

How to display all records from a table with RedBeanPHP?

From Dev

Delete all records from table that starts with <

From Dev

Mysql - How to get all records being in other column in the same table

From Dev

Mass-Update Form w/VBA & SQL - ID not pulling correctly, records not being written to temp table

From Dev

To display all records of table?

From Dev

Count all records in a table

Related Related

  1. 1

    Finding duplicate records from table and deleting all but one with latest date

  2. 2

    Deleting all table records from core data database using relationship

  3. 3

    Finding duplicate records from table and deleting all but one with latest date

  4. 4

    deleting records from table using SQLAlchemy

  5. 5

    deleting records from a table where there is an inner join

  6. 6

    Deleting billions of records from Oracle Table containing trillions of records

  7. 7

    Deleting billions of records from Oracle Table containing trillions of records

  8. 8

    SQL Loop not adding all input records to temp table

  9. 9

    Error from Entity Framework when deleting records from table with foreign key

  10. 10

    records from database being loaded when they are not called

  11. 11

    SQL Server perform a delete on all child records when deleting from a parent

  12. 12

    Retriving all records from left table and matching records from right

  13. 13

    Paste 10000 records from excel sheet into a temp table

  14. 14

    create temp table from join contained + 65 milion of records Mysql

  15. 15

    Deleting all records apart from one, and only with a field value in 'type'

  16. 16

    Preventing users from deleting records in one table that have related records in another table

  17. 17

    Deleting checked records from table in asp.net

  18. 18

    Keep all records from one table when using UNION ALL with other table

  19. 19

    Not getting all records from Table A in left join

  20. 20

    Get all records from table - EclipseLink

  21. 21

    Get all records from azure table storage

  22. 22

    Getting all records from database to Vaadin Table

  23. 23

    Get all records from another table with pivot

  24. 24

    How to display all records from a table with RedBeanPHP?

  25. 25

    Delete all records from table that starts with <

  26. 26

    Mysql - How to get all records being in other column in the same table

  27. 27

    Mass-Update Form w/VBA & SQL - ID not pulling correctly, records not being written to temp table

  28. 28

    To display all records of table?

  29. 29

    Count all records in a table

HotTag

Archive