c# - 根据行号删除多行

我想根据从 WinForm 传入的行号删除行。当然，删除一行时，ROW_NUMBER() 计算的输出总是会更新，所以行号也会改变，因此我想一次性完成而不是循环。

我有这个代码:

CREATE PROCEDURE Delete_Record (@RowNum INT)
AS
  ;WITH REC_ROW AS
  (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Record Number]) AS RN
        FROM User_Data
  )
  DELETE
    FROM REC_ROW
    WHERE RN IN (@RowNum)

当我输入时:

 exec Delete_Record @RowNum = '1,2'

它产生错误:



如果我将 @RowNum INT 更改为 @RowNum varchar(max) ，它只会产生错误消息:



当我对值进行硬编码时:

;WITH REC_ROW AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Record Number]) AS RN
        FROM User_Data
)
DELETE
  FROM REC_ROW
  WHERE RN IN (1,2)

它将成功删除第 1 行和第 2 行。问题是如何将其集成到存储过程中并输入 '1,2' 以传递给 IN 子句？

最佳答案

如果您要传递比 1,2 更复杂的字符串，您可能需要考虑使用表值参数 (TVP)。

CREATE TYPE dbo.Integers AS TABLE
(
  RowNumber INT PRIMARY KEY
);

现在，您可以创建存储过程并从应用程序传入 DataTable 或其他集合，而无需费心构建逗号分隔的字符串或尝试将它们分开。

CREATE PROCEDURE dbo.Delete_Record
  @RowNums dbo.Integers READONLY
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH REC_ROW AS (...your CTE unchanged here...)
  DELETE REC_ROW
   FROM REC_ROW INNER JOIN @RowNums AS r
   ON r.RowNumber = REC_ROW.RN;
END
GO

如果您真的想使用拆分功能:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  (
    SELECT Item = y.i.value('(./text())[1]', 'int')
    FROM ( SELECT x = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>')
        + '</i>').query('.')) AS a
    CROSS APPLY x.nodes('i') AS y(i));
GO

现在你的存储过程:

CREATE PROCEDURE dbo.Delete_Record -- always use a schema prefix!
  @RowNums VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH REC_ROW AS
  (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Record Number]) AS RN
        FROM dbo.User_Data
  )
  DELETE REC_ROW
    FROM REC_ROW
    INNER JOIN dbo.SplitInts(@RowNums, ',') AS r
    ON r.Item = REC_ROW.RN;
END
GO

但我向您保证，TVP 会表现得更好。

关于c# - 根据行号删除多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18340401/