您可以使用表值参数来解决这个问题.应用层看起来像

You could use table valued parameters to hand this. The application layer would look something like

C#

var tvp = new DataTable();
tvp.Columns.Add("Id", typeof(int));

foreach(var id in RecIdsToDelete)
    tvp.Rows.Add(new {id});

var connection = new SqlConnection("your connection string");

var delete = new SqlCommand("your stored procedure name", connection)
{
  CommandType = CommandType.StoredProcedure
};

delete
  .Parameters
  .AddWithValue("@ids", tvp)
  .SqlDbType = SqlDbType.Structured;

delete.ExecuteNonQuery();

SQL

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'IDList')
BEGIN
    CREATE TYPE IDList AS TABLE(ID INTEGER)
END


CREATE PROCEDURE School.GroupStudentDelete
(                                         
        @IDS IDLIST READONLY      
)                                         
AS

SET NOCOUNT ON;

BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @Results TABLE(id INTEGER)

        DELETE 
        FROM TblName 
        WHERE Id IN (SELECT ID FROM @IDS)        

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        PRINT ERROR_MESSAGE();

        ROLLBACK TRANSACTION
        THROW; -- Rethrow exception
END CATCH
GO

与构建字符串相比，这种方法有许多优点

There are a number of advantages to this approach over building strings

• 您避免在应用层创建查询，从而创建关注点分离
• 您可以更轻松地测试执行计划和优化查询
• 您不太容易受到 SQL 注入攻击，因为您给定的方法将无法使用参数化查询来构建 IN 子句
• 代码更具可读性和说明性
• 您最终不会构建过长的字符串

性能

关于 TVP 在大型数据集上的性能有一些考虑.

There are some considerations about the performance of TVPs on large datasets.

因为 TVP 是变量，所以它们不编译统计数据.这意味着查询优化器有时可以伪造执行计划.如果发生这种情况，有几个选项:

Because TVPs are variables, they do not compile statistics. This means the query optimizer can fudge the execution plan sometimes. If this happens there a couple options :

• 在索引存在问题的任何 TVP 语句上设置 OPTION (RECOMPILE)
• 将 TVP 写入本地临时文件并在那里设置索引

这是一篇关于 TVP 的精彩文章，有一个很好的部分关于性能方面的考虑，以及什么时候会发生什么.

Here is a great article on TVP's with a good section on performance considerations, and what to expect when.

因此，如果您担心会达到字符串参数的限制，那么表值参数可能是您要走的路.但最终，如果不了解您正在使用的数据集的更多信息，就很难说.

So if you are worried about hitting limits on string parameters, the table valued parameters might be the way to go. But in the end, it is hard to say without knowing more about the data set you are working with.