SQL中如何用While Loop返回多值？日志备份遇NULL问题

你的问题核心有两个：

1. @LogbackupFile 初始值为NULL，导致While循环的条件@LogbackupFile IS NOT NULL一开始就不成立，循环根本没执行，最后输出自然是NULL。
2. 单个变量只能存储单一值，没法直接装下多个日志备份文件路径，这也是你用Set只能拿到最新日志的原因。

下面提供几种针对性的解决办法：

方法1：直接查询输出所有符合条件的日志文件（最推荐） #

SQL天生擅长集合操作，不需要变量或循环，直接查询所有满足条件的记录即可，这也是效率最高的方式：

DECLARE @DatabaseName varchar(500) = 'ns_lots_of_vlfs', 
        @DiffDate datetime = null, 
        @RestoreDate datetime = '2023-01-30 12:00:06.000', 
        @FullBackupFile nvarchar(max), 
        @FullBackupDate datetime 

-- 获取最新的全量备份信息
SELECT TOP 1 
    @FullBackupFile = bmf.physical_device_name, 
    @FullBackupDate = bs.backup_finish_date 
FROM msdb.dbo.backupmediafamily bmf 
INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id 
WHERE bs.type = 'D' 
  AND bs.database_name = @DatabaseName 
  AND bs.backup_finish_date <= @RestoreDate 
ORDER BY backup_finish_date DESC

-- 直接输出所有符合条件的日志备份文件（按备份时间升序，恢复时需按此顺序执行）
SELECT 
    bmf.physical_device_name AS LogBackupFile
FROM msdb.dbo.backupmediafamily bmf 
INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id 
WHERE bs.database_name = @DatabaseName 
  AND bs.backup_finish_date <= @RestoreDate 
  AND bs.backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
  AND bs.type = 'L' 
  AND bmf.physical_device_name LIKE '%Log%'
ORDER BY bs.backup_finish_date ASC

方法2：将所有日志文件路径拼接为逗号分隔的字符串（适合变量传递场景） #

如果必须把结果存入单个变量，可通过字符串拼接实现：

DECLARE @DatabaseName varchar(500) = 'ns_lots_of_vlfs', 
        @DiffDate datetime = null, 
        @RestoreDate datetime = '2023-01-30 12:00:06.000', 
        @FullBackupFile nvarchar(max), 
        @FullBackupDate datetime,
        @LogBackupFiles nvarchar(max) = '' -- 初始化空字符串

-- 获取最新的全量备份信息
SELECT TOP 1 
    @FullBackupFile = bmf.physical_device_name, 
    @FullBackupDate = bs.backup_finish_date 
FROM msdb.dbo.backupmediafamily bmf 
INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id 
WHERE bs.type = 'D' 
  AND bs.database_name = @DatabaseName 
  AND bs.backup_finish_date <= @RestoreDate 
ORDER BY backup_finish_date DESC

-- 拼接所有日志文件路径
SELECT 
    @LogBackupFiles = CONCAT(@LogBackupFiles, CASE WHEN @LogBackupFiles <> '' THEN ',' ELSE '' END, bmf.physical_device_name)
FROM msdb.dbo.backupmediafamily bmf 
INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id 
WHERE bs.database_name = @DatabaseName 
  AND bs.backup_finish_date <= @RestoreDate 
  AND bs.backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
  AND bs.type = 'L' 
  AND bmf.physical_device_name LIKE '%Log%'
ORDER BY bs.backup_finish_date ASC

-- 输出拼接后的结果
SELECT @LogBackupFiles AS LogBackupFiles

方法3：用循环逐行获取（不推荐，集合操作更高效） #

如果一定要用循环，需先初始化变量并通过标识列逐行处理：

DECLARE @DatabaseName varchar(500) = 'ns_lots_of_vlfs', 
        @DiffDate datetime = null, 
        @RestoreDate datetime = '2023-01-30 12:00:06.000', 
        @FullBackupFile nvarchar(max), 
        @FullBackupDate datetime,
        @LogbackupFile nvarchar(max)

-- 获取最新的全量备份信息
SELECT TOP 1 
    @FullBackupFile = bmf.physical_device_name, 
    @FullBackupDate = bs.backup_finish_date 
FROM msdb.dbo.backupmediafamily bmf 
INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id 
WHERE bs.type = 'D' 
  AND bs.database_name = @DatabaseName 
  AND bs.backup_finish_date <= @RestoreDate 
ORDER BY backup_finish_date DESC

-- 创建带标识列的临时表
IF OBJECT_ID('tempdb..#TempTable_log') IS NOT NULL 
DROP TABLE #TempTable_log

SELECT 
    ROW_NUMBER() OVER(ORDER BY bs.backup_finish_date ASC) AS RowNum,
    bmf.physical_device_name
INTO #TempTable_log
FROM msdb.dbo.backupmediafamily bmf 
INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id 
WHERE bs.database_name = @DatabaseName 
  AND bs.backup_finish_date <= @RestoreDate 
  AND bs.backup_finish_date > ISNULL(@DiffDate, @FullBackupDate) 
  AND bs.type = 'L' 
  AND bmf.physical_device_name LIKE '%Log%'

-- 循环遍历临时表
DECLARE @CurrentRow int = 1, @TotalRows int = (SELECT COUNT(*) FROM #TempTable_log)

WHILE @CurrentRow <= @TotalRows
BEGIN
    SELECT @LogbackupFile = physical_device_name FROM #TempTable_log WHERE RowNum = @CurrentRow
    PRINT @LogbackupFile -- 打印当前日志文件路径
    SET @CurrentRow = @CurrentRow + 1
END

-- 输出所有结果
SELECT * FROM #TempTable_log

注意：方法1是最优选择，SQL的集合操作远胜循环；方法3仅适合特殊的逐行处理场景，性能较差。

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