SQL Server存储过程循环传值查询及批量删表最优方案咨询

嘿，针对你问的两个SQL Server技术问题，我来分享下实际工作里常用的靠谱方案，都是经过验证的最佳实践哦～

首先得明确：SQL Server是为集合操作设计的，行级遍历（比如游标）尽量少用，性能差。优先用下面这些方案：

推荐方案1：表值参数（TVP） #

这是最优雅、性能最好的方式，适合批量传入结构化数据，还能保证类型安全。

步骤示例： #

1. 先创建一个自定义表类型：

CREATE TYPE dbo.IdList AS TABLE (Id INT NOT NULL PRIMARY KEY);

2. 编写存储过程，接收这个表类型参数：

CREATE PROCEDURE dbo.GetTargetData
    @InputIds dbo.IdList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 直接用JOIN做集合查询，效率拉满
    SELECT t.*
    FROM YourTargetTable t
    INNER JOIN @InputIds i ON t.Id = i.Id;
END

3. 调用存储过程时传入数据：

DECLARE @Ids dbo.IdList;
INSERT INTO @Ids (Id) VALUES (101), (102), (103);
EXEC dbo.GetTargetData @InputIds = @Ids;

优点：类型安全、支持索引（定义主键会自动生成统计信息）、批量处理性能最优，适合复杂场景复用。

推荐方案2：临时表/表变量 + 字符串拆分 #

如果不需要复用自定义类型，或者传入的是逗号分隔的字符串，用这种方式更灵活（SQL Server 2016+支持STRING_SPLIT）：

CREATE PROCEDURE dbo.GetTargetDataFromString
    @IdList NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TempIds TABLE (Id INT NOT NULL);
    -- 拆分字符串到临时表
    INSERT INTO @TempIds (Id)
    SELECT value FROM STRING_SPLIT(@IdList, ',') WHERE ISNUMERIC(value) = 1;

    -- 同样用JOIN做查询
    SELECT t.*
    FROM YourTargetTable t
    INNER JOIN @TempIds i ON t.Id = i.Id;
END

注意：如果是SQL Server 2016之前的版本，需要自己写一个字符串拆分函数。

万不得已用游标 #

如果业务逻辑必须逐行处理（比如每一行要调用其他存储过程、做复杂判断），用快速只进游标，比默认游标性能好很多：

CREATE PROCEDURE dbo.ProcessIdsRowByRow
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CurrentId INT;
    -- 定义快速只进游标
    DECLARE id_cursor CURSOR FAST_FORWARD FOR
        SELECT Id FROM YourSourceTable WHERE Status = 'Pending';

    OPEN id_cursor;
    FETCH NEXT FROM id_cursor INTO @CurrentId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 执行逐行逻辑，比如调用其他存储过程
        EXEC dbo.ProcessSingleId @Id = @CurrentId;
        FETCH NEXT FROM id_cursor INTO @CurrentId;
    END

    CLOSE id_cursor;
    DEALLOCATE id_cursor;
END

缺点：行级处理性能远不如集合操作，尽量避免使用。

临时表的方式是可行的，但不是最优，还有更高效的批量处理方案：

最优方案：动态SQL + STRING_AGG（SQL Server 2017+） #

直接生成批量DROP TABLE语句，一次执行，性能最高，代码也最简洁。

示例代码： #

DECLARE @DropSql NVARCHAR(MAX);

-- 替换成你返回表名的SELECT查询
SELECT @DropSql = STRING_AGG(QUOTENAME(table_name), '; DROP TABLE ')
FROM (
    SELECT name AS table_name
    FROM sys.tables
    WHERE name LIKE 'Temp_%' -- 比如删除前缀为Temp_的表
) AS TablesToDrop;

-- 生成完整的DROP语句并执行
IF @DropSql IS NOT NULL
BEGIN
    SET @DropSql = 'DROP TABLE ' + @DropSql + ';';
    EXEC sp_executesql @DropSql;
END

关键细节：用QUOTENAME()处理表名，防止SQL注入，同时兼容带空格、关键字的表名（比如[User]、[Order Details]）。

优点：集合操作，一次执行，性能拉满，代码简洁易维护。

临时表 + WHILE循环（你当前的方案） #

这个方案适合需要逐行做额外操作的场景（比如记录删除日志、判断表是否存在再删除），但性能不如批量动态SQL：

-- 先把要删除的表名存入临时表
SELECT name AS table_name
INTO #TablesToDrop
FROM sys.tables
WHERE name LIKE 'Temp_%';

DECLARE @TableName NVARCHAR(128);
WHILE EXISTS (SELECT 1 FROM #TablesToDrop)
BEGIN
    -- 取出一个表名
    SELECT TOP 1 @TableName = table_name FROM #TablesToDrop;

    -- 生成动态SQL删除（同样用QUOTENAME防注入）
    DECLARE @DropSql NVARCHAR(MAX) = 'DROP TABLE ' + QUOTENAME(@TableName) + ';';
    EXEC sp_executesql @DropSql;

    -- 移除已处理的表名
    DELETE FROM #TablesToDrop WHERE table_name = @TableName;
END

DROP TABLE #TablesToDrop;

缺点：逐行处理，表数量多的时候性能会明显下降。

不推荐：游标 #

和WHILE循环逻辑类似，但性能更差，除非有特殊的逐行业务需求，否则完全没必要用。

总结 #

• 如果只是单纯批量删除表，动态SQL + STRING_AGG是最优选择；
• 如果需要逐行做额外操作（比如日志、校验），临时表+WHILE是可以接受的方案。

内容的提问来源于stack exchange，提问作者flashleo