CODEWITHSUNDEEP
sqlsql-server-2008-r2sql-delete
user2392980
user2392980

Reputation: 57

SQL stored procedure - Deleting each row that matches input list

I need to make a stored procedure that takes a userId and a list of categoryIds, and delete any row that matches, from a specific table.

Pseudo code:

@categorylist int[]
@userId int

foreach(category as c in @categorylist)
{
DELETE FROM HelpWith as h
WHERE c.categoryId = h.categoryId
AND h.userId = @userId
}

Now I know that you are not supposed to use loops in SQL (Not that this would work anyways...) Does anyone know how to make a stored procedure that could do this?

Upvotes: 3

Views: 6324

Answers (2)

gbn
gbn

Reputation: 432200

For SQL Server and versions 2008+ you can use Table Valued Parameters

CREATE TYPE Categorylist AS TABLE (ID int);
GO

CREATE PROCEDURE DoStuff
   @userId int,
   @list Categorylist READONLY
AS
...
DELETE FROM HelpWith 
      WHERE categoryId IN (SELECT ID FROM @list)
      AND userId = @userId
...

Upvotes: 3

Edwin Stoteler
Edwin Stoteler

Reputation: 1228

Make a table variable out of @Categorylist

@Categorylist table (ID int)

DELETE FROM HelpWith 
WHERE categoryId IN (SELECT ID FROM @Categorylist)
AND userId = @userId

Upvotes: 4

Related Questions