代码之家 › 专栏 › 技术社区 › User987

批量删除操作过程不工作

stored-procedures sql-server asp.net sql c#

User987 · 技术社区 · 7 年前

alter procedure [dbo].[zsp_deleteEndedItems]
(
@ItemIDList nvarchar(max)
)
as
delete from 
SearchedUserItems
WHERE EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@ItemIDList,',') S1 WHERE ItemID=S1.val)

参数IDList的传递方式如下所示:

124125125,125125125...etc etc

拆分字符串函数如下所示:

ALTER FUNCTION [dbo].[SplitStringProduction]
(
  @string nvarchar(max),
  @delimiter nvarchar(5)
) RETURNS @t TABLE
(
  val nvarchar(500)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@string,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(500)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

这将删除ID下“SearcheduserItems”表中的所有项目:

124125125 and 125125125

但出于某种原因,在我选择查看之后:

select * from SearchedUserItems
where itemid in('124125125','125125125')

记录还在那里。。。

1 回复 | 直到 7 年前

Thom A 7 年前

如注释中所述,另一种选择是使用表类型参数。不过,这会做出一些假设(一些评论),让您走上正确的道路:

CREATE TYPE dbo.IDList AS TABLE (ItemID int NOT NULL); --Assumed int datatype;
GO

ALTER PROC dbo.zsp_deleteEndedItems @ItemIDList dbo.IDList READONLY AS

    DELETE SUI
    FROM dbo.SearchedUserItems SUI
         JOIN @ItemIDList IDL ON SUI.ItemID = IDL.ItemID;
GO

--Example of usage

DECLARE @ItemList dbo.IDList;

INSERT INTO @ItemList
VALUES(123456),(123457),(123458);

EXEC dbo.zsp_deleteEndedItems @ItemList;

GO

关于内联表值函数的问题,下面是一个这样的示例,我很快写了这个示例,它提供了下一个1000个数字的计数表:

CREATE FUNCTION dbo.NextThousand (@Start int)
RETURNS TABLE
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)
    )
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 + @Start AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3; --1,000
GO

iTVF的重要之处在于它只有一个语句,即 RETURN 陈述将表声明为返回类型变量,将数据插入其中,并返回该变量,将其转换为多行TVF;它们的表现要慢得多。