如何修改SQL查询以支持多@agentid参数并循环执行？

一、多值参数声明方式 #

用表变量替代单个变量存储多个AgentID，灵活支持任意数量的ID值：

DECLARE @Startdate datetime,
        @Enddate datetime,
        -- 表变量存储多AgentID
        @AgentIDs TABLE (EmployeeID VARCHAR(100))

SET @startdate = '4/17/2023'
SET @enddate = '8/07/2023'
-- 插入示例多AgentID
INSERT INTO @AgentIDs (EmployeeID)
VALUES ('12345'),('6789'),('98763'),('246810')

二、循环执行逻辑（游标实现） #

用游标逐个读取表变量中的AgentID，依次执行DELETE、INSERT和报表查询，完全匹配你需要的"按单个ID循环"需求：

SET NOCOUNT ON

-- 声明游标，遍历每个AgentID
DECLARE @CurrentAgentID VARCHAR(100)
DECLARE AgentCursor CURSOR FOR
SELECT EmployeeID FROM @AgentIDs

-- 启动游标
OPEN AgentCursor
FETCH NEXT FROM AgentCursor INTO @CurrentAgentID

-- 循环处理每个AgentID
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 1. 清理目标Agent的历史数据
    DELETE FROM tblGenesys
    FROM tblGenesys 
    INNER JOIN tblUsers2 AS u 
        ON tblGenesys.AgentID = u.GenesysLogon 
        AND tblGenesys.AgentSummaryDate = u.ExtractDate
    WHERE [AgentSummaryDate] >= @Startdate 
      AND [AgentSummaryDate] < DATEADD(dd,1,@Enddate) 
      AND [GenesysGroupName] = 'US' 
      AND u.employeeid = @CurrentAgentID

    -- 2. 插入目标Agent的新数据
    INSERT INTO [dbo].[tblGenesys]
    ( 
        [Username], [AgentSummaryDate], [GenesysGroupName], [AgentNameRaw], [AgentIDRaw], [LastName], [FirstName], [AgentID],
        AgentIDSuffix, [Account], [AccountGroup], [AccountOrganization], [Supervisor], [SVP], [JobTitle], [Client_Paid],
        [TotalDuration], [InboundCount], [InboundTime], [OutboundCount], [OutboundTime], [InternalCount], [InternalTime],
        [HoldTime], [ACWTime], [WaitTime], [DialTime], [BreakTime], FaxEmailTime, [LeadTime], [LunchTime], [Meet_TrainTime],
        [OtherTime], [ProjectTime], [SystemIssueTime], [NotReadyTime], [HRTime], [ManualWorkTime], [OtherChannelTime], [Transferred], 
        [WeekOf], [DayOfWeek], [MonthOf], GenesysLogon
    )
    SELECT 
        g.[Username], [AgentSummaryDate], [GroupName], [AgentNameRaw], [AgentIDRaw], g.[LastName], g.[FirstName], [AgentID],
        AgentIDSuffix, g.[Account], g.[AccountGroup], g.[AccountOrganization], g.[Supervisor], g.[SVP], g.[JobTitle], [Client_Paid],
        [TotalDuration], [InboundCount], [InboundTime], [OutboundCount], [OutboundTime], [InternalCount], [InternalTime],
        [HoldTime], [ACWTime], [WaitTime], [DialTime], [BreakTime], FaxEmailTime, [LeadTime], [LunchTime], [Meet_TrainTime],
        [OtherTime], [ProjectTime], [SystemIssueTime], [NotReadyTime], [HRTime], [ManualWorkTime], [OtherChannelTime], [TranMake], 
        DATEADD(DD, 2 - DATEPART(DW, DATEADD(DD, 0, agentsummarydate)), DATEADD(DD, 0, agentsummarydate)) as WeekOf, 
        LEFT(DATENAME(dw, CAST(agentsummarydate AS DATE)), 3) AS DayOfWeek, 
        LEFT(DATENAME(mm, CAST(agentsummarydate AS DATE)), 3) + '-' + RIGHT(DATENAME(YY, CAST(agentsummarydate AS DATE)), 2) AS [MonthOf], 
        g.AgentID
    FROM [uv_Genesys_AgentSummaryGroup_CST] as g 
    INNER JOIN tblusers2 as u 
        ON g.AgentID = u.GenesysLogon 
        AND g.AgentSummaryDate = u.ExtractDate
    WHERE [AgentSummaryDate] >= @Startdate 
      AND [AgentSummaryDate] < DATEADD(dd,1,@Enddate) 
      AND u.EmployeeID = @CurrentAgentID
    ORDER BY [AgentSummaryDate], [LastName] , [FirstName]

    -- 3. 输出当前Agent的报表统计
    SELECT 
        @CurrentAgentID AS [AgentID],
        count(*) as [RecordsInsertedTable], 
        min(D.[AgentSummaryDate]) as [FirstDate],
        min(D.[WeekOf]) as [FirstWeekOf],
        max(D.[AgentSummaryDate]) as [LastDate],
        max(D.[WeekOf]) as [LastWeekOf],
        min(D.[CreateDate]) as [FirstDBCreateDate],
        max(D.[CreateDate]) as [LastDBCreateDate]
    FROM [dbo].[tblGenesys] as D with(nolock) 
    INNER JOIN tblUsers2 as u 
        ON D.AgentID = u.GenesysLogon 
        AND D.AgentSummaryDate = u.ExtractDate
    WHERE D.[AgentSummaryDate] >= @Startdate 
      AND D.[AgentSummaryDate] < DATEADD(dd,1,@Enddate) 
      AND D.[GenesysGroupName] = 'US' 
      AND u.EmployeeID = @CurrentAgentID

    -- 读取下一个AgentID
    FETCH NEXT FROM AgentCursor INTO @CurrentAgentID
END

-- 关闭并释放游标资源
CLOSE AgentCursor
DEALLOCATE AgentCursor

三、优化方案：集合操作替代循环（可选） #

如果不需要单独输出每个Agent的报表，用集合操作一次性处理所有Agent，效率远高于循环：

SET NOCOUNT ON

-- 一次性删除所有目标Agent的历史数据
DELETE FROM tblGenesys
FROM tblGenesys 
INNER JOIN tblUsers2 AS u 
    ON tblGenesys.AgentID = u.GenesysLogon 
    AND tblGenesys.AgentSummaryDate = u.ExtractDate
INNER JOIN @AgentIDs a ON u.employeeid = a.EmployeeID
WHERE [AgentSummaryDate] >= @Startdate 
  AND [AgentSummaryDate] < DATEADD(dd,1,@Enddate) 
  AND [GenesysGroupName] = 'US'

-- 一次性插入所有目标Agent的新数据
INSERT INTO [dbo].[tblGenesys]
( 
    [Username], [AgentSummaryDate], [GenesysGroupName], [AgentNameRaw], [AgentIDRaw], [LastName], [FirstName], [AgentID],
    AgentIDSuffix, [Account], [AccountGroup], [AccountOrganization], [Supervisor], [SVP], [JobTitle], [Client_Paid],
    [TotalDuration], [InboundCount], [InboundTime], [OutboundCount], [OutboundTime], [InternalCount], [InternalTime],
    [HoldTime], [ACWTime], [WaitTime], [DialTime], [BreakTime], FaxEmailTime, [LeadTime], [LunchTime], [Meet_TrainTime],
    [OtherTime], [ProjectTime], [SystemIssueTime], [NotReadyTime], [HRTime], [ManualWorkTime], [OtherChannelTime], [Transferred], 
    [WeekOf], [DayOfWeek], [MonthOf], GenesysLogon
)
SELECT 
    g.[Username], [AgentSummaryDate], [GroupName], [AgentNameRaw], [AgentIDRaw], g.[LastName], g.[FirstName], [AgentID],
    AgentIDSuffix, g.[Account], g.[AccountGroup], g.[AccountOrganization], g.[Supervisor], g.[SVP], g.[JobTitle], [Client_Paid],
    [TotalDuration], [InboundCount], [InboundTime], [OutboundCount], [OutboundTime], [InternalCount], [InternalTime],
    [HoldTime], [ACWTime], [WaitTime], [DialTime], [BreakTime], FaxEmailTime, [LeadTime], [LunchTime], [Meet_TrainTime],
    [OtherTime], [ProjectTime], [SystemIssueTime], [NotReadyTime], [HRTime], [ManualWorkTime], [OtherChannelTime], [TranMake], 
    DATEADD(DD, 2 - DATEPART(DW, DATEADD(DD, 0, agentsummarydate)), DATEADD(DD, 0, agentsummarydate)) as WeekOf, 
    LEFT(DATENAME(dw, CAST(agentsummarydate AS DATE)), 3) AS DayOfWeek, 
    LEFT(DATENAME(mm, CAST(agentsummarydate AS DATE)), 3) + '-' + RIGHT(DATENAME(YY, CAST(agentsummarydate AS DATE)), 2) AS [MonthOf], 
    g.AgentID
FROM [uv_Genesys_AgentSummaryGroup_CST] as g 
INNER JOIN tblusers2 as u 
    ON g.AgentID = u.GenesysLogon 
    AND g.AgentSummaryDate = u.ExtractDate
INNER JOIN @AgentIDs a ON u.EmployeeID = a.EmployeeID
WHERE [AgentSummaryDate] >= @Startdate 
  AND [AgentSummaryDate] < DATEADD(dd,1,@Enddate)
ORDER BY [AgentSummaryDate], [LastName] , [FirstName]

-- 按Agent分组输出报表统计
SELECT 
    u.EmployeeID AS [AgentID],
    count(*) as [RecordsInsertedTable], 
    min(D.[AgentSummaryDate]) as [FirstDate],
    min(D.[WeekOf]) as [FirstWeekOf],
    max(D.[AgentSummaryDate]) as [LastDate],
    max(D.[WeekOf]) as [LastWeekOf],
    min(D.[CreateDate]) as [FirstDBCreateDate],
    max(D.[CreateDate]) as [LastDBCreateDate]
FROM [dbo].[tblGenesys] as D with(nolock) 
INNER JOIN tblUsers2 as u 
    ON D.AgentID = u.GenesysLogon 
    AND D.AgentSummaryDate = u.ExtractDate
INNER JOIN @AgentIDs a ON u.EmployeeID = a.EmployeeID
WHERE D.[AgentSummaryDate] >= @Startdate 
  AND D.[AgentSummaryDate] < DATEADD(dd,1,@Enddate) 
  AND D.[GenesysGroupName] = 'US'
GROUP BY u.EmployeeID

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