2

I am trying the write a stored procedure for Delete. But for performance we have changed the delete sql query to use IN operation. I want write the procedure where it accepts comma separated IDs to procedure.

I have tried to write a procedure where it accepts single Entry ID. The procedure is like below. 

CREATE PROCEDURE DeleteListEntry
        @entryid int

AS

DELETE FROM  LIST_ITEMS
 WHERE ENTRY_ID = @entryid;
go

I want to know how to convert above procedure to accept bulk entries. The sql query for it is below- 

DELETE FROM LIST_ITEMS WHERE ENTRY_ID IN (id1, id2, id2, ... );
bash
  • 123
  • 1
  • 8
  • 1
    I recommend against the solution in that duplicate, Bash (and @TabAlleman), it uses a `WHILE`. `STRING_SPLIT`, a XML Splitter, or a tally table splitter are far better options if you want to go down the delimited value route. – Thom A May 14 '19 at 13:59
  • I've added a duplicate for delimited list, which offers a far better solution than that added previously. Specifically, take note of the answer by @Pரதீப். – Thom A May 14 '19 at 14:03
  • As an FYI, an `IN` is essentially a shortcut for a list of `OR`s. So `WHERE x IN (1,2)` is the same as `WHERE x = 1 OR x = 2`. – Shawn May 14 '19 at 14:06
  • Flagging a question as a duplicate isn't necessarily an endorsement of the accepted answer. Many answers have been added to the duplicate, including one suggesting table-valued parameters. Readers should peruse all of the answers and choose the one that best fits their needs, and hopefully upvote it. – Tab Alleman May 14 '19 at 14:25
  • @TabAlleman Unfortunately, one of the drawbacks to SO (and the Internet in general) is that some things live way too long. The first dupe is 10 years old, and both deal with deprecated versions of SQL. Parsing through answers that old can help perpetuate bad habits that have been fixed in more recent versions of a software, as evidenced by the answers to this question. Someone looking for this answer is very likely to just click on the "This question already has an answer" links, rather than read the more modern and relevant ones here. I'd see it more as a Related Question than a Duplicate. – Shawn May 14 '19 at 14:44

1 Answers1

6

One method is to use a Table type parameter, and a JOIN. Assuming ENTRY_ID is an int:

CREATE TYPE dbo.EntryList AS TABLE (Entry_ID int NOT NULL);
GO

CREATE PROC dbo.DeleteListEntry @Entries dbo.EntryList READONLY AS
BEGIN

    DELETE LI
    FROM LIST_ITEMS LI
         JOIN @Entries E ON LI.ENTRY_ID = E.Entry_ID ;
END;

Then you can call the SP by doing:

DECLARE @Entries dbo.EntryList;
INSERT INTO @Entries
VALUES(1),(2),(3);

EXEC dbo.DeleteListEntry @Entries;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • You beat me to it....again. – Luis Cazares May 14 '19 at 13:49
  • Sorry Luis! Maybe next time ;) – Thom A May 14 '19 at 13:50
  • 2
    Interesting, a down vote. Any reason? I've fixed the alias issue, if that was the reason. If not, please do share how your reasons, so that I can improve the answer. We don't learn if we don't know what we "did wrong". – Thom A May 14 '19 at 13:50
  • @Larnu Can't even imagine any reason. This is exactly how i used to do it for years. Works like a charm. – Serg May 14 '19 at 13:55
  • 3
    I'm guessing it's because you answered a duplicate question. Doesn't matter if the answer is perfect, people just downvote for not "following the rules" – Luis Cazares May 14 '19 at 13:55
  • 2
    Personally, I don't mind downvotes, but i see them as an opportunity to learn. Unfortunately I, and the OP who is asking the question, learn nothing if the voter doesn't comment. Shame, but at least this'll answer the OP's question. I don't actually agree with the link duplicate here, so I'll add some more, as it uses a `WHILE`. I'll add a comment under the question on that,. – Thom A May 14 '19 at 13:58
  • @Larnu Agreed. And I wish there was a way for the community to vote to remove a downvote without an associated comment. They don't help future readers at all and actually harm legitimate answers. :-/ – Shawn May 14 '19 at 14:14
  • JOIN operation is expensive right ? that is the only option we have ? – bash May 14 '19 at 18:23
  • 1
    Why do you think `JOIN` is expensive @bash? They're only expensive if you have a poorly designed database, or query (or a hell of a lot of them). This `JOIN` is not going to make things "expensive". Passing a delimited list, and splitting it out is probably going to be more costly. – Thom A May 14 '19 at 18:32
  • cool, when do you think EntryList table will be deleted ? After executing the procedure? any advice on how to invoke this procedure using Java – bash May 14 '19 at 19:14
  • 1
    I have no experience with Java (not since College 15 years ago anyway), @bash . If you've done some research, and fail to find the answer, you would be better off asking a new question asking how to use table-value parameters in Java (don't forget to show your attempts in your new question). – Thom A May 14 '19 at 19:21