如何编写存储过程以接受使用 IN 操作删除行的集合? [英] How to write stored procedures to accept a collection to delete rows using IN operation?

问题描述

我正在尝试为删除编写存储过程.但是为了性能，我们已将删除 sql 查询更改为使用 IN 操作.我想编写接受逗号分隔 ID 的过程.

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.

我尝试编写一个接受单个条目 ID 的过程.程序如下.

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

我想知道如何将上述程序转换为接受批量输入.它的 sql 查询如下 -

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, ... );

推荐答案

一种方法是使用 Table 类型参数和一个 JOIN.假设 ENTRY_ID 是一个 int:

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;

然后您可以通过执行以下操作来调用 SP:

Then you can call the SP by doing:

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

EXEC dbo.DeleteListEntry @Entries;

这篇关于如何编写存储过程以接受使用 IN 操作删除行的集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！