CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Chris
Chris

Reputation: 3139

How to use declared table values to delete from a table?

I'm passing a delimited string to a stored procedure that enters the values into the declared table when it runs into the delimiter,

Here is my Stored Procedure.

 Alter PROCEDURE s_BulkDeleteTest
(
@IDString VarChar(200)
)
AS
-- Creating Variables
DECLARE @numberLength int
DECLARE @numberCount int
DECLARE @TheIDs VarChar(200)
DECLARE @sTemp VarChar(100) -- to hold single characters

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

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

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

This all works fine for adding the values to the @T table, but then I added this..

delete from [Subjects]
where (select TheIDs from @T) = SubjectID

that threw an error about there being more than one value in the declared table @T. So I was wondering how can I use the values in @T and delete all those ID's from my Subjects table.

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You need to use in:

delete from [Subjects]
    where SubjectId in (select TheIDs from @T);

A result set with multiple rows cannot be equal to a single value.

EDIT:

The expression (select TheIds from @T) returns a set of values. The = operator works on scalar values, not sets. So, it doesn't normally work with this construct. The in operator compares a scalar to a set. so it does work.

There is one exception. When the subquery returns one row and one column, then it is converted to a scalar value. So, the expression would work if there were one row returned, or if you forced one row, as in:

    where SubjectId = (select top 1 TheIDs from @T);

Of course, in would work in this situation as well.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69594

If TheIDs has any null values using IN operator will delete unexpect rows. I would suggest using EXISTS operator something like this...

DELETE FROM [Subjects] 
WHERE EXISTS 
             (SELECT 1
              FROM @T
              WHERE [Subjects].SubjectId = TheIDs)

Upvotes: 2

Related Questions